Rowan Worth wrote: > if you want to know the latest prices, is this legal? > > SELECT currency, price FROM Prices GROUP BY currency HAVING time = MAX(time);
It is not legal ANSI SQL, and most other databases will complain. While the WHERE clause allows to filter out rows from the table, the HAVING clause is evaluated _after_ the groups have already been created, and allows to filter out entire groups (i.e., output rows). In this particular query, "HAVING time = MAX(time)" throws away any group where the "time" value does not happen to be the maximum value in the group. What makes this query actually work is that SQLite has a (somewhat undocumented) extension where using a single MAX() or MIN() determines the row in a group where unaggregated expressions come from (instead of using some random row). But this is just a side effect of the expression in the HAVING clause; using HAVING like this is an extremely misleading way to write this query. Better write the query like this: SELECT currency, price, MAX(time) FROM Prices GROUP BY currency; (Same extension, so this is not legal ANSI SQL either.) Neither your original query nor this query work if there is more than one MIN()/MAX(), or if some other aggregation function is used. Regards, Clemens
