On 22 November 2017 at 17:08, Igor Korot <ikoro...@gmail.com> 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 popular fruit near the top. In that case the fruit.sort_order could represent relative popularity of the fruit entries. Database idea is to store the data and then retrieve them in any way > you want at any given time. > > So all you need to know that there is a record inserted into the table > "fruit" on the schema > "garden". > Then when the time comes by you can retrieve the records with the > "ORDER BY" clause. > Whether you will sort the data by alphabet - fruit_name" or by number > increment - "Sort_order" > doesn't really matter. > Inserting the record is an implementation detail which shouldn't > bother you at all. > Actually, it interests me. If I knew insertions and updates in the fruit table were mostly for unpopular fruits, then Peter Nichvolodov's trigger solution ( https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html) might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL solution ( https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html) might more be efficient. However, querying may be slower. > Unless.... you can sow us that the time required to retrieve the > sorting data will SIGNIICANTLY > differ in both cases. > > I am ready to hear arguments against this approach. ;-) > > Thank you. > > > > > > > On 22 November 2017 at 00:11, Igor Korot <ikoro...@gmail.com> wrote: > > > >> Simon, > >> > >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> > >> wrote: > >> > > >> > > >> > On 21 Nov 2017, at 10:09pm, Jens Alfke <j...@mooseyard.com> wrote: > >> > > >> >>> On Nov 21, 2017, at 1:56 AM, R Smith <rsm...@rsweb.co.za> 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’s > >> say you sort rows by date. You’ve already got some entries from 2015 in > >> your database, and some from 2017. Someone now inserts 60 entries from > >> 2016, and to be ‘helpful’, they insert them in chronological order. > Wham, > >> this immediately hits that case. > >> > > >> > Yes, if you use this method, you do need to renumber them every so > >> often. You assess this when you’re working out (before + after) / 2, > and > >> you do it using something like the double-UPDATE command someone came up > >> with earlier. > >> > > >> > But that just brings us back to the question of why OP wants to store > ID > >> numbers which might change. > >> > >> Homework exercise? > >> Stupid requirements? > >> > >> Thank you. > >> > >> > > >> > Simon. > >> > _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@mailinglists.sqlite.org > >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
