On 22 Nov 2017, at 8:30pm, Shane Dev <devshan...@gmail.com> 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 represent relative popularity of the fruit entries.
Storing sort order is not a good way to do this. If you want to list fruits in
order of popularity do this:
CREATE TABLE fruits (id INTEGER PRIMARY KEY,
name TEXT COLLATE NOCASE,
numberSold INTEGER);
CREATE INDEX fruits_numberSold ON fruits (numberSold);
Do not mess with the ID value: never change it, never display it. That’s just
for the computer.
When a new fruit is introduced INSERT it with a value of 0 for numberSold.
When a fruit is sold, UPDATE its row to increase the numberSold value.
When you want your list do
SELECT name,numberSold FROM fruits ORDER BY numberSold DESC
You get your list in the order you want. At no point are the order positions
stored in the table. The numbers mean nothing and can change at any minute, so
it would be pointless to do so.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
