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

[Simon Slavin]

On 29 Sep 2015, at 8:05am, Rowan Worth <rowanw at dugeo.com> wrote:

> 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 see others have already told you not to use this, but I'd like to work 
towards a better command.  To find the latest price for any one currency do 
this:

SELECT currency,price FROM Prices
        WHERE currency='EUR'
        ORDER BY day DESC
        LIMIT 1;

This finds all the rows for the right currency, then sorts them so that the 
latest is at the top, then returns just the top row.  If SQL has an index like 
the following:

CREATE INDEX Prices_cd ON Prices (currency, day);

then it will use this index to jump straight to the correct row to return, and 
it will return the result very quickly.  Even better, this index will speed up 
the other SELECT involved in this.

So to use the above command with every currency you need another SELECT which 
will pick out each currency you have data for.  You can do that like this:

SELECT DISTINCT currency FROM Prices;

So you should be able to combine the two:

SELECT currency AS thisCCy,price FROM Prices
        WHERE price = (SELECT price FROM Prices
                        WHERE currency = thisCCy
                        ORDER BY day DESC
                        LIMIT 1);

or, because we have an index which will make this faster,

SELECT currency AS thisCCy,price FROM Prices
        WHERE day = (SELECT day FROM Prices
                        WHERE currency = thisCCy
                        ORDER BY day DESC
                        LIMIT 1);

I don't like using sub-SELECT and I would love to see another reader rephrase 
this using 'WITH' or a VIEW.

As an incidental note, I advise you to store the time-of-day of each price in 
your Prices file even if you store only one price per day.  Any auditor of your 
system will find their job easier if they can easily figure out which price 
(from those throughout the day) you decided to use.  It may not make things 
easier in the day-to-day running of the system but it will make things easier 
if it's ever audited, or if you every have to discuss the data with a customer.