SQL in any flavor isn't going to get what you want directly, as you've seen
in these posts.
The data is in your database for a reason. Its data. It isn't supposed to
be manipulated in the way you want to do in this thread.
Displaying something in a particular order is done at the application
le
On 22 Nov 2017, at 8:30pm, Shane Dev wrote:
> Imagine I have a GUI element with a drop down list of fruit. The source of
> the list is my fruit table and it may have many entries. It might more
> convenient to list the popular fruit near the top. In that case the
> fruit.sort_order could repres
>> Hi, Shane,
>> What I don't understand is why do you need to do that?
>Imagine I have a GUI element with a drop down list of fruit. The
>source of
>the list is my fruit table and it may have many entries. It might
>more
>convenient to list the popular fruit near the top. In that case the
>fruit
Hi,
On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev wrote:
> On 22 November 2017 at 17:08, Igor Korot wrote:
>
>> Hi, Shane,
>>
>>
>> What I don't understand is why do you need to do that?
>>
>
> Imagine I have a GUI element with a drop down list of fruit. The source of
> the list is my fruit table a
On 22 November 2017 at 17:08, Igor Korot wrote:
> Hi, Shane,
>
>
> What I don't understand is why do you need to do that?
>
Imagine I have a GUI element with a drop down list of fruit. The source of
the list is my fruit table and it may have many entries. It might more
convenient to list the pop
> Why do I want store ID numbers
> whose values may change? Why not.
Because that's not what the row id column is for. Not strictly. That's why
it's called 'id' - it's an identification field. You can't (shouldn't) be
using it for other means. A database requirement later might need that
column to
Hi, Shane,
On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev wrote:
> Hi Igor,
>
> Homework exercise? No, this is purely a hobby project in my free time. My
> goal is see how much logic can moved from application code to the database.
>
> Why do I want store ID numbers whose values may change? Why not.
On 11/22/17, 1:43 AM, "sqlite-users on behalf of R Smith"
wrote:
> Oh there are many valid reasons why to have Order in data, one I use
> regularly is to dictate the process flow in manufacturing where some thing
> needs to go to machine Y before it can move on to machine X, or process E,
> f
On 2017/11/22 8:40 AM, Shane Dev wrote:
However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even
Shane.
Below is a simple benchmark you can play with to decide if that trigger is
fast enough for your application. On the time scale of human thinking and
reaction time, I've found SQLite code quite responsive and magnitudes
easier to maintain than the equivalent application code.
FYI, that tri
Shane Dev wrote:
> Why do I want store ID numbers whose values may change? Why not.
Because the name "ID" implies that its value _identifies_ the row.
If it changes, it is not an ID.
> Obviously, this would be bad idea if the ID column was referenced by
> other column / table. In that case, I wou
On 2017/11/22 2:29 AM, Jens Alfke wrote:
On Nov 21, 2017, at 2:48 PM, Simon Slavin wrote:
But that just brings us back to the question of why OP wants to store ID
numbers which might change.
When I’ve run into this before, the requirement has been to support lists with
customizable order
Hi Igor,
Homework exercise? No, this is purely a hobby project in my free time. My
goal is see how much logic can moved from application code to the database.
Why do I want store ID numbers whose values may change? Why not. Obviously,
this would be bad idea if the ID column was referenced by othe
On Tue, Nov 21, 2017, 11:10 PM Jens Alfke, wrote:
>
> It’s a lot better to use strings, and just increase the length of the
> string as necessary. So to insert in between “A” and “C” you add “B”, then
> to insert between “A” and “B” you add “AM”, etc.
>
Except that you can't insert before "A" :)
Am 22.11.2017 um 01:29 schrieb Jens Alfke:
When I’ve run into this before, the requirement has been to support lists with
customizable ordering, like an outliner where the user can freely drag the rows
up and down.
Yep.
And therefore such cases should be handled at the App-Level IMO...
Ther
> On Nov 21, 2017, at 2:48 PM, Simon Slavin wrote:
>
> But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
When I’ve run into this before, the requirement has been to support lists with
customizable ordering, like an outliner where the user
Simon,
On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin wrote:
>
>
> On 21 Nov 2017, at 10:09pm, Jens Alfke wrote:
>
>>> On Nov 21, 2017, at 1:56 AM, R Smith wrote:
>>>
>>> That assumes you are not starting from an integer part (like 4000) and
>>> hitting the exact same relative insert spot every
On 21 Nov 2017, at 10:09pm, Jens Alfke wrote:
>> On Nov 21, 2017, at 1:56 AM, R Smith wrote:
>>
>> That assumes you are not starting from an integer part (like 4000) and
>> hitting the exact same relative insert spot every time, which /can/ happen,
>> but is hugely unlikely.
>
> Not to bea
> On Nov 21, 2017, at 1:56 AM, R Smith wrote:
>
> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/ happen,
> but is hugely unlikely.
Not to beat this into the ground, but: it’s not that unlikely. Let’
On 2017/11/21 7:35 AM, Jens Alfke wrote:
On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote:
INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana')
This gives you a value of 1.5, and puts the new entry in the right place.
This solution (which comes up every time this problem is discussed, it seem
> On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote:
>
> INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana')
>
> This gives you a value of 1.5, and puts the new entry in the right place.
This solution (which comes up every time this problem is discussed, it seems)
is attractive but not very scal
v
>Sent: Monday, 20 November, 2017 14:31
>To: SQLite mailing list
>Subject: Re: [sqlite] how into insert row into middle of table with
>integer primary key
>
>Hi Ryan,
>
>Nice trick - changing the sign of ID. I agree that changing an
>existing
>record's ID value would
Shane. If you're dead set on paying the cost for brute force mid table id
insertion, take a look at INSTEAD OF triggers:
https://sqlite.org/lang_createtrigger.html
Your example would look like this:
CREATE VIEW fruit_ins AS SELECT * FROM fruit;
CREATE TRIGGER fruit_ins INSTEAD OF INSERT ON fruit
Oops, didn't concentrate, that query should best be:
WITH NewOrder(nid,norder) AS (
SELECT F1.id, (SELECT COUNT(*) * 100 FROM fruit AS F2 WHERE
F2.SortOrder < F1.SortOrder)
FROM fruit AS F1
ORDER BY F1.id
-- This last ORDER BY is important as it forces the above correlated
subquer
On 2017/11/20 11:31 PM, Shane Dev wrote:
Hi Ryan,
Nice trick - changing the sign of ID. I agree that changing an existing
record's ID value would cause problems for any other field / table that
referenced this key.
If I used your idea of adding a SortOrder column incremented in steps of x,
as
On 20 Nov 2017, at 9:31pm, Shane Dev wrote:
> I would to prefer to avoid this solution because it involves mutable state
> (the RowCount variable) which is the "root of all evil" (bugs). Is there an
> SQL statement which could reset the gaps back to x?
As others have written, the thing you’re
Hi Ryan,
Nice trick - changing the sign of ID. I agree that changing an existing
record's ID value would cause problems for any other field / table that
referenced this key.
If I used your idea of adding a SortOrder column incremented in steps of x,
as you suggested, the gaps would start to disap
On 2017/11/20 6:33 PM, Igor Korot wrote:
On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote:
The reason why this is bad? Mostly a primary Key serves as a lookup for
other tables linking to a very specific record. Imagine your query that
added fruit to recipes where needed has the fruit's primary
Hi,
On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote:
> This question pops up from time to time.
>
> I will show a correct query script to achieve this below, but I want to
> emphasize what others have said: Data in an RDBMS has no intrinsic order,
> it's all SETs, and if you artificially bestow o
This question pops up from time to time.
I will show a correct query script to achieve this below, but I want to
emphasize what others have said: Data in an RDBMS has no intrinsic
order, it's all SETs, and if you artificially bestow order to the data
itself (as opposed to the eventual output)
On 20 Nov 2017, at 2:57pm, Clemens Ladisch wrote:
> Simon Slavin wrote:
>> UPDATE fruit SET id = id+1 WHERE id >=2;
>
> This is unlikely to work because some ID values can conflict in the
> middle of the execution.
Which in fact violates formal requirements. Im SQL it is proper to have
cons
If you want to maintain something like a user-selected display order, I would
suggest adding an explicit “display order” column.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo
nsert row into middle of table with integer
primary key
UPDATE fruit SET id = id+1 WHERE id >=2;
INSERT …
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/list
Simon Slavin wrote:
> UPDATE fruit SET id = id+1 WHERE id >=2;
This is unlikely to work because some ID values can conflict in the
middle of the execution.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://maili
On 19 Nov 2017, at 8:37pm, Shane Dev wrote:
> sqlite> select * from fruit;
> id|name
> 1|apple
> 2|pear
> 3|kiwi
>
> Is there an easy way to insert 'banana' between apple and pear while still
> maintaining a consistent order of the ID field?
>
> desired result -
>
> sqlite> select * from fru
Shane Dev wrote:
> CREATE TABLE fruit(id integer primary key, name text);
>
> id|name
> 1|apple
> 2|pear
> 3|kiwi
>
> Is there an easy way to insert 'banana' between apple and pear while still
> maintaining a consistent order of the ID field?
It would be possible, but not easy, especially not in p
Let's say I have a table of fruit -
sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, name text);
with some entries -
sqlite> select * from fruit;
id|name
1|apple
2|pear
3|kiwi
Is there an easy way to insert 'banana' between apple and pear while still
maintaining a consistent order
37 matches
Mail list logo
