The correlated subquery is fine if your SQL must be portable, but will return n rows if the a value is duplicated (n times) within a group. The bare field select only works in SQLite and is probably faster, with the caveat that the row the bare field values are taken from is "random". Since you seem to guarantee unique a within each b/c group, both will return exactly one row for every b/c group (with the c and d values "randomly selected" from a set of 1 rows, in case of the bare field select).
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto: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 which value is selected from the bare column. To (hopefully) clarify my purpose: I'm adding a row (with incrementing "a") each time "d" is changed for a given combination of "b" and "c". But the way I want to query is: for each "c" with a given "b", find me the most recent "d" (in my example, the "b" I gave was 1). R Smith's approach does the trick nicely! Thanks, Hamish On 3 April 2017 at 10:48, Hick Gunter <h...@scigames.at> wrote: > I am not sure I correctly understand what you want. > > This is the value of d associated with a randomly chosen record from > the group of records having the highest value of a tha also fulfills > b == 1; > > SELECT MAX(a),d FROM x WHERE b=1; > > If you want the value of d within each group of records sharing a > value of c, try > > SELECT MAX(a),c,d FROM x WHERE b=1 GROUP BY c ORDER BY 1 DESC, 2 ASC; > > See also http://sqlite.org/lang_select.html > > "Side note: Bare columns in an aggregate queries. The usual case is that all > column names in an aggregate query are either arguments to aggregate > functions or else appear in the GROUP BY clause. A result column which > contains a column name that is not within an aggregate function and that does > not appear in the GROUP BY clause (if one exists) is called a "bare" column. > Example: > > SELECT a, b, sum(c) FROM tab1 GROUP BY a; > > > In the query above, the "a" column is part of the GROUP BY clause and so each > row of the output contains one of the distinct values for "a". The "c" column > is contained within the sum() aggregate function and so that output column is > the sum of all "c" values in rows that have the same value for "a". But what > is the result of the bare column "b"? The answer is that the "b" result will > be the value for "b" in one of the input rows that form the aggregate. The > problem is that you usually do not know which input row is used to compute > "b", and so in many cases the value for "b" is undefined. > > Special processing occurs occurs when the aggregate function is either min() > or max(). Example: > > SELECT a, b, max(c) FROM tab1 GROUP BY a; > > When the min() or max() aggregate functions are used in an aggregate query, > all bare columns in the result set take values from the input row which also > contains the minimum or maximum. So in the query above, the value of the "b" > column in the output will be the value of the "b" column in the input row > that has the largest "c" value. There is still an ambiguity if two or more of > the input rows have the same minimum or maximum value or if 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 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 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 > > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. > > > _______________________________________________ > 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
