Hi guys,
Imagine a DB storing a history of currency exchange rate info.
CREATE TABLE Prices (
id INTEGER PRIMARY KEY,
day INTEGER,
currency TEXT,
price FLOAT);
Assume 'day' increases monotonically and there is at most one price
recorded per currency per day - if you want to know the latest prices, is
this legal?
SELECT currency, price FROM Prices GROUP BY currency HAVING time =
MAX(time);
I've seen this approach used and my reading of
https://www.sqlite.org/lang_select.html#resultset hasn't left me confident
that it is correct SQL, but in practice it seems to work fine... eg:
insert into Prices (day, currency, price) values (1, 'EUR', 1.86);
insert into Prices (day, currency, price) values (1, 'USD', 1.20);
insert into Prices (day, currency, price) values (2, 'EUR', 1.92);
insert into Prices (day, currency, price) values (2, 'USD', 1.18);
insert into Prices (day, currency, price) values (3, 'USD', 1.24);
/* price for EUR on day 3 intentionally left blank */
sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day =
MAX(day);
EUR|1.92
USD|1.24
MIN also seems to work:
sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day =
MIN(day);
EUR|1.86
USD|1.2
As an aside, the query definitely doesn't work to get prices for a specific
day:
sqlite> SELECT currency, price FROM Prices GROUP BY currency HAVING day = 2;
EUR|1.92
I think the following explains the last result:
> If a HAVING clause is a non-aggregate expression, it is evaluated with
> respect to an arbitrarily selected row from the group.
And the arbitrary row for EUR just happened to be day=2 here. But the
documentation doesn't mention non-aggregate queries with a group by
clause...
> If the HAVING clause is an aggregate expression, it is evaluated across
all
> rows in the group.
I'm not sure how to interpret this in the context of the "day = MAX(day)"
expression though - does "evaluated across all rows" mean that rows
excluded by the clause are discarded?
If not then I suspect this query is not legit... but I don't feel I've
understood everything so any clarification is appreciated!
Cheers,
-Rowan
