[sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause?

[James K. Lowden]

On Tue, 29 Sep 2015 15:05:42 +0800
Rowan Worth <rowanw at dugeo.com> wrote:

> Imagine a DB storing a history of currency exchange rate info.
> 
> CREATE TABLE Prices (
>     id INTEGER PRIMARY KEY,
>     day INTEGER,
>     currency TEXT,
>     price FLOAT);

Better for your purpose would be: 

CREATE TABLE Prices (
    day INTEGER not NULL,
    currency TEXT not NULL,
    price FLOAT not NULL,
    PRIMARY KEY(day, currency)
);

You'll note that your queries don't use the id column, and nothing in
your table definition ensures there's only one price per currency per
day.  

If it were me, I'd make day TEXT and add a constraint that requires the
month portion be between 1 and 12.

> if you want to know the latest prices, is this legal?
> 
> SELECT currency, price FROM Prices GROUP BY currency HAVING time =
> MAX(time);

What you want is called existential quantification.  The standard SQL
for it is a correlated subquery, viz:

        SELECT *
        FROM Prices as p 
        where exists (
                select 1 from Prices
                where currency = p.currency
                GROUP BY currency
                HAVING p.time = MAX(time)
        );

That version might be slower in SQLite than LIMIT 1, although it
shouldn't be.  You'll have to test it.  The advantage to you is that
it's standard SQL.  It will work on any SQL DBMS, and will help you
think about the issue in terms of sets instead of having to rely on
a crutch like LIMIT.  

--jkl