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.
