Hi, On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev <devshan...@gmail.com> wrote: > 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.
Then have a popularity column in the table and update it with every single hit using trigger. Then do the query to fill out you list with "ORDER BY popularity". Once again - how the records are inserted is implementation detail which shouldn't be of the concern in any situations. Thank you. P.S.: Basically you are trying to create a problem where there is no problem and a nice and simple solution. > > >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
