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
