Thanks, Keith. I think I need the sub-select because I don't just want the (d) for the max(a) for the grouping of (c), but for the combination of the grouping (c) and the given value (b).
On 3 April 2017 at 14:32, Keith Medcalf <kmedc...@dessus.com> wrote: > select max(a), c, d > from x > where b == 1 > group by c > order by 1 desc; > > index on (b, c, a) > > So, you are grouping by c where b=1, and within each grouping returning the > grouping (c), the maximum value of (a) for that grouping, and (d) from one of > perhaps multiple rows that contain the max(a) within that group. > > Or perhaps: > > select max(a), c, d > from x > where b == 1; > > Which finds the max(a) where b==1, and returns that value and the value of c > and d from one of the rows containing that maximum (1 row returned). > > index on (b, a) > > The former returns all groups of c with the top one being the one row > returned by the latter. > >> -----Original Message----- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sent: Monday, 3 April, 2017 02:51 >> To: SQLite mailing list >> Subject: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY >> >> Ah. My purpose is to determine "d for the most recent c with b=1", >> with "most recent" being "largest a". >> >> My query had been working for this, but I'd only been adding >> monotonically increasing values for "a" in my tests. I just tried >> doing otherwise and found that I had been relying on an implementation >> detail. >> >> Is there any way I can perform this collation at query time, or do I >> need to do it programmatically later on? >> >> Thanks, >> Hamish >> >> >> >> >> >> On 3 April 2017 at 00:41, Keith Medcalf <kmedc...@dessus.com> wrote: >> > >> > What is the purpose of the ORDER BY? >> > >> > The value of a used for the order by is from some random row in the >> grouping of c. Are there relationships between a, b, c, d that you have >> not documented nor told us about? >> > >> > In any case, your most efficient index is on (b, c). >> > >> > The order by is useless. >> > >> >> -----Original Message----- >> >> From: sqlite-users [mailto:sqlite-users- >> boun...@mailinglists.sqlite.org] >> >> On Behalf Of Hamish Allan >> >> Sent: Sunday, 2 April, 2017 17:28 >> >> To: sqlite-users@mailinglists.sqlite.org >> >> Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY >> >> >> >> Given a table: >> >> >> >> CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); >> >> >> >> the query: >> >> >> >> SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; >> >> >> >> shows the following plan, without indexes: >> >> >> >> 0|0|0|SCAN TABLE x >> >> 0|0|0|USE TEMP B-TREE FOR GROUP BY >> >> 0|0|0|USE TEMP B-TREE FOR ORDER BY >> >> >> >> I can create an index to cover the WHERE clause: >> >> >> >> CREATE INDEX b_index ON x (b); >> >> >> >> which gives the plan: >> >> >> >> 0|0|0|SEARCH TABLE x USING INDEX b_index (b=?) >> >> 0|0|0|USE TEMP B-TREE FOR GROUP BY >> >> 0|0|0|USE TEMP B-TREE FOR ORDER BY >> >> >> >> or I can create an index to cover the GROUP BY clause: >> >> >> >> DROP INDEX b_index; >> >> CREATE INDEX c_index ON x (c); >> >> >> >> which gives the plan: >> >> >> >> 0|0|0|SCAN TABLE x USING INDEX c_index >> >> 0|0|0|USE TEMP B-TREE FOR ORDER BY >> >> >> >> but I don't seem to be able to create a combined index to cover both >> >> the WHERE and GROUP BY clauses (let alone the ORDER BY clause). >> >> >> >> Am I missing something, or is this just not possible? >> >> >> >> If it's not possible, which is the more efficient of those indexes -- >> >> or is there a third way, using an index for the ORDER BY, which would >> >> be more efficient still? >> >> >> >> Thanks, >> >> Hamish >> >> _______________________________________________ >> >> 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 >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
