Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

to:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Allan Gesendet: Montag, 03. April 2017 16:24 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Thanks, Hick. I now understand that it's undefined wh

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

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

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

f the query > contains more than one min() and/or max() aggregate function. Only the > built-in min() and max() functions work this way." > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Hamish All

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

This second one also has the advantage that I understand what's being expressed!  This works perfectly, thank you! (and thank you to all others that replied). I'm not sure I understand the indexing, though. The query plan without indexes is: 0|0|0|SCAN TABLE x AS x1 0|0|0|EXECUTE CORRELATED

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

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

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

sers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Allan Gesendet: Montag, 03. April 2017 10:51 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Ah. My purpose is to determine "d for t

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Another option, if a is simple and a suitable index exists, is to simply get the max in a correlated subquery, so this should work faster even: SELECT c,d FROM x AS x1 WHERE b=1 AND a=(SELECT MAX(x2.a) FROM x AS x2 WHERE x2.b=x1.b AND x2.c=x1.c) This needs no grouping because the sub-query

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

On 2017/04/03 10:51 AM, Hamish Allan wrote: 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

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

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

On 3 Apr 2017, at 12:27am, Hamish Allan wrote: > SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; Your problem comes down to this: If you are GROUPing BY c, why do you want ORDER BY a ? If you remove the "ORDER BY a" clause then the ideal index would be on (b, c). But

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

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

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

I had nearly the same question a month ago (Subject: Index usefulness for GROUP BY). In my case, the best index was on the WHERE clause because it eliminated the scan and returned only the few important rows for the other clauses. However, the best result will depend on how many rows are