How can I select rows with most recent timestamp for each key value?

sql greatest-n-per-group
franklynd picture franklynd · Jun 26, 2013 · Viewed 219.8k times · Source

I have a table of sensor data. Each row has a sensor id, a timestamp, and other fields. I want to select a single row with latest timestamp for each sensor, including some of the other fields.

I thought that the solution would be to group by sensor id and then order by max(timestamp) like so:

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable 
GROUP BY sensorID 
ORDER BY max(timestamp);

This gives me an error saying that "sensorField1 must appear in the group by clause or be used in an aggregate."

What is the correct way to approach this problem?

Answer

fancyPants picture fancyPants · Jun 26, 2013

For the sake of completeness, here's another possible solution:

SELECT sensorID,timestamp,sensorField1,sensorField2 
FROM sensorTable s1
WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID)
ORDER BY sensorID, timestamp;

Pretty self-explaining I think, but here's more info if you wish, as well as other examples. It's from the MySQL manual, but above query works with every RDBMS (implementing the sql'92 standard).

Related questions

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700 I need to select each distinct home holding the maximum value of datetime. Result would be: id home datetime …

Read More
SQL select only rows with max value on a column

I have this table for documents (simplified version here): +------+-------+--------------------------------------+ | id | rev | content | +------+-------+--------------------------------------+ | 1 | 1 | ... | | 2 | 1 | ... | | 1 | 2 | ... | | 1 | 3 | ... | +------+-------+--------------------------------------+ How do I select one row per id and only the greatest rev? With the above data, the result …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More