SELECT DISTINCT * FROM table ORDER BY field1,field2,field3,field4 DESC,field5;
to do the whole sorting and de-duplication in one step ... assuming you want to report duplicate entire rows only once ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Tuesday, 26 September, 2017 11:37 >To: SQLite mailing list >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
