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

Question

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 1

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).