On 24 Feb 2016, at 3:15am, admin at shuling.net wrote: > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER); > > Now if I want to select from MyTable, and sort the result based on F1 > (ascendant), and for two records with same F1, then sort based on > F2(ascendant), then sort based on F3(ascendant) should I use the following > : > > SELECT * FROM MyTable ORDER BY F1, F2, F3?
Yes. Why not try it ? Download the SQLite shell tool from the SQLite download page and experiment with it. Instructions here: <https://www.sqlite.org/cli.html> > Moreover, since the sort will be performed frequently, to improve the > performance, I want to add a field F4, then update the table so that all the > records will have a unique F4 value which is the order sequence based on the > above order by clause, thus if I want to select again, I do not need to use > ORDER BY F1, F2, F3 any more, instead, I can use: > > SELECT * FROM MyTable ORDER BY F4 > > Is that feasible? If yes, how do to that? You don't do it like that. Instead you create an index: CREATE INDEX m_f1f2f3 ON MyTable (F1, F2, F3) This does something similar to what you were trying to do with F4, but it means that the database has a copy of that table pre-sorted into the order your SELECT needs. The next time you execute the SELECT SQLite will automatically spot that it has an idea index already prepared and it will use it. You will not notice much difference in the amount of time taken with your sample database because it has only 7 rows, but once you have 7000 rows the figures should be easy to tell apart. An index is automatically updated every time the data in the table changes. In other words, once you have created it you can forget about it. Simon.
