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

User · Answer

This can de done in a relatively elegant way using SELECT DISTINCT, as follows:

SELECT DISTINCT ON (sensorID)
sensorID, timestamp, sensorField1, sensorField2 
FROM sensorTable
ORDER BY sensorID, timestamp DESC;

The above works for PostgreSQL (some more info here) but I think also other engines. In case it's not obvious, what this does is sort the table by sensor ID and timestamp (newest to oldest), and then returns the first row (i.e. latest timestamp) for each unique sensor ID.

In my use case I have ~10M readings from ~1K sensors, so trying to join the table with itself on a timestamp-based filter is very resource-intensive; the above takes a couple of seconds.

User · Answer

There is one common answer I haven t see here yet  which is the Window Function   It is an alternative to the correlated sub-query  if your DB supports it   SELECT sensorID timestamp sensorField1 sensorField2  FROM       SELECT sensorID timestamp sensorField1 sensorField2           ROW NUMBER   OVER              PARTITION BY sensorID             ORDER BY timestamp           AS rn     FROM sensorTable s1 WHERE rn   1 ORDER BY sensorID  timestamp    I acually use this more than correlated sub-queries   Feel free to bust me in the comments over effeciancy  I m not too sure how it stacks up in that regard

User · Answer

You can join the table with itself  on sensor id   and add left timestamp  lt  right timestamp as join condition  Then you pick the rows  where right id is null  Voila  you got the latest entry per sensor   http   sqlfiddle com   9 45147 37  SELECT L   FROM sensorTable L LEFT JOIN sensorTable R ON L sensorID   R sensorID AND L timestamp  lt  R timestamp WHERE isnull  R sensorID    But please note  that this will be very resource intensive if you have a little amount of ids and many values  So  I wouldn t recommend this for some sort of Measuring-Stuff  where each Sensor collects a value every minute  However in a Use-Case  where you need to track  Revisions  of something that changes just  sometimes   it s easy going

User · Answer

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

User · Answer

I had mostly the same problem and ended up a a different solution that makes this type of problem trivial to query   I have a table of sensor data  1 minute data from about 30 sensors   SensorReadings- gt  timestamp value idSensor    and I have a sensor table that has lots of mostly static stuff about the sensor but the relevant fields are these   Sensors- gt  idSensor Description tvLastUpdate tvLastValue        The tvLastupdate and tvLastValue are set in a trigger on inserts to the SensorReadings table  I always have direct access to these values without needing to do any expensive queries   This does denormalize slightly  The query is trivial   SELECT idSensor Description tvLastUpdate tvLastValue  FROM Sensors   I use this method for data that is queried often   In my case I have a sensor table  and a large event table  that have data coming in at the minute level AND dozens of machines are updating dashboards and graphs with that data   With my data scenario the trigger-and-cache method works well

User · Answer

You can only select columns that are in the group or used in an aggregate function  You can use a join to get this working  select s1    from sensorTable s1 inner join      SELECT sensorID  max timestamp  as mts   FROM sensorTable    GROUP BY sensorID    s2 on s2 sensorID   s1 sensorID and s1 timestamp   s2 mts

User · Answer

WITH SensorTimes As      SELECT sensorID  MAX timestamp   LastReading     FROM sensorTable    GROUP BY sensorID   SELECT s sensorID s timestamp s sensorField1 s sensorField2  FROM sensorTable s INNER JOIN SensorTimes t on s sensorID   t sensorID and s timestamp   t LastReading

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

Examples related to sql

Examples related to greatest-n-per-group