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
