Thank you so much - I will test this as soon as I get home! -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, September 26, 2017 1:37 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?
On 26 Sep 2017, at 6:24pm, Ron Barnes <rbar...@njdevils.net> wrote: > I need to sort them as follows... > > Sort Field 1 Ascending > Sort Field 2 Ascending WITHIN field 1 > Sort Field 3 Ascending WITHIN field 2 WITHIN field 1 Sort Field 4 > Descending WITHIN field 3 WITHIN field 2 WITHIN field 1 <== This is a > Date field and the most current (Highest) Date to float up Sort Field > 5 Ascending WITHIN field 4 WITHIN field 3 WITHIN field 2 WITHIN field > 1 Trivial in any SQL engine including SQLite. It looks something like SELECT * FROM MyTable ORDER BY field1,field2,field3,field4 DESC,field5 SQL users do that sort of thing all the time. However, if you use just the above command, SQLite will have to perform this sorting of 600M records each time you execute the command, which could take quite a long time — minutes or hours depending on your hardware. So for any flavour of SQL you would probably tell it to create an index … CREATE INDEX m_12345 ON MyTable (field1,field2,field3,field4 DESC,field5) This tells SQL to perform the sorting and save the resulting order on disk. Then every time you perform the above SELECT command SQL notices it already has the sort-order saved and just uses that one. This can change the amount of time taken to a few seconds. My one concern in reading your post is how your dates are formatted. When putting your date fields into your SQL table you will have to ensure that dates are saved as a day number, or as text which naturally sorts into date order, e.g. YYYY/DD/MM. You should not expect SQL to sort text such as "19 October 16" correctly. 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
