On 5 Sep 2012, at 2:03pm, Richard Hipp <d...@sqlite.org> wrote: > *The problem to solve* is this: We want to restrict the output of the > query above to show only the entry with the smallest MTIME for each > distinct ID. > > As of SQLite version 3.7.11 > <http://www.sqlite.org/releaselog/3_7_11.html>the problem can be > solved like this: > > SELECT id, a, b, ..., min(mtime) > FROM tab > GROUP BY id > ORDER BY min(mtime) DESC; > > SQLite, unique among SQL database engines as far as I know, will choose for > A, B, C, ... the values of those columns that correspond to the row that > satisfies the min(mtime) aggregate. And so this actually works. On SQLite > versions prior to 3.7.11, the values of A, B, C, .. would be from any > arbitrary row having the corresponding ID. And on more strictly conforming > SQL implementations (ex: PostgreSQL) the query will not even parse because > the GROUP BY clause does not contain every column that is not within an > aggregate function. But the query does work, and works efficiently and > well, in SQLite and seems very convenient. > > *Question*: Is there a (better) standard SQL way to do the same thing?
For the sake of completeness, I supply an answer you may reject: do it in two SELECTs. The first one just identifies something unique about the row which has the entry with the smallest mtime -- perhaps the mtime value itself if that's unique and indexed, otherwise rowid. The second one gets the values of a, b, c, etc. from that row. Will work for all SQL engines, and is not dependent on obscure (even if documented) behaviour of GROUP BY ORDER BY. I've noticed that the majority of people posting here seem to prefer one long complicated query which requires detailed knowledge of SQL syntax to two simple queries. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
