2

I have the following table

table prices
col id
col price
col currency_id
col timestamp (unix)

I would like to group the currencies by their ID and only show the most recent record based off the timestamp.

I have the following query which groups correctly but does not get the most recent records, always seems to get the first rows in the table rather than the last.

SELECT 
id,
price,
currency_id,
timestamp
FROM prices
GROUP BY currency_id
ORDER BY timestamp DESC

I did get lazy and attempt this which gets the last 6 records but the order isn't always the same and means I have to modify the query each time I add a new currency (currently 6).

SELECT 
id,
price,
currency_id,
timestamp
FROM prices
ORDER BY timestamp DESC
LIMIT 6

It has been a few years since I have done any MySQL and feel like this is the most simplest thing to do lol :(

Story Teller
  • 417
  • 1
  • 4
  • 14

1 Answers1

3

Here is a sqlfiddle to test http://sqlfiddle.com/#!9/2a4e63/3

Start with this:

SELECT currency_id, 
       Max(timestamp) 
FROM   prices 
GROUP  BY currency_id

Then integrate that to main query:

SELECT * 
FROM   prices 
WHERE  ( currency_id, timestamp ) IN (SELECT currency_id, 
                                             Max(timestamp) 
                                      FROM   prices 
                                      GROUP  BY currency_id)
Armen Orduyan
  • 81
  • 5