Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

Hi Ron, It may be that increasing the sqlite cache size will substantially reduce the time for either the CREATE INDEX or the SELECT ... ORDER BY instructions (depending upon which method you choose). https://sqlite.org/pragma.html#pragma_cache_size On Wed, Sep 27, 2017 at 1:58 AM, jungle

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

Hi there, Just because I'm interested, I'm wondering if you can identify your hardware, and how long it takes your system to do your desired operation on such a large number of records. Do let us know which option you performed the query with as well. P.S. For best results, I'd recommend using

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

 Than you! -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Tuesday, September 26, 2017 4:31 PM To: SQLite mailing list Subject: Re: [sqlite] Is there a way to

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

uh.. UNIQUE... DISTINCT... , On Tue, Sep 26, 2017 at 4:30 PM, Stephen Chrzanowski wrote: > > > On Tue, Sep 26, 2017 at 1:36 PM, Simon Slavin > wrote: > >> >> >> My one concern in reading your post is how your dates are formatted. >> When putting your

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

On Tue, Sep 26, 2017 at 1:36 PM, Simon Slavin wrote: > > > 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

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

That is exactly what I want to do -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Tuesday, September 26, 2017 3:04 PM To: SQLite mailing list Subject: Re: [sqlite] Is

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

Thank you very much! I will research the two suggestions below. As for your sort assumption, you are correct. A A 1 2 A A 2 1 A B 1 2 A B 1 3 A C 1 1 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Warren Young

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

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

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

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

Re: [sqlite] Is there a way to perform a muti-level sort and extract of large data sets?

On Sep 26, 2017, at 11:24 AM, Ron Barnes wrote: > > I have approximately 600 million records that need to be sorted Where is the data now? > There are 18 table entries. You mean 18 columns per row, right? > I also need to deduplicate the records based upon the sorted

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 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