SQL Query to return last value from a number of tags

Question

I hope you might be able to help. I'm a novice at SQL so this one is starting to bug me.

Currently I am collecting data every day for a Meter Name. This data is currently being logged in a table with the columns TimeStamp, Name, Value. However I would like to create a query which will only return the most recent (last) value recorded against each Name in the table.

I've built this query so far but the Top 1 syntax doesn't seem to be what I need.

SELECT Top 1 (DataLog.Timestamp), MeterTags.Name, DataLog.Value
FROM Meters
INNER JOIN MeterTags
ON Meters.MeterId = MeterTags.MeterId
INNER JOIN DataLog
ON MeterTags.MeterTagId = DataLog.MeterTagId
WHERE Meters.MeterTypeId = 8
GROUP By MeterTags.Name, DataLog.Timestamp

Any advice you could give would be appreciated.

Thanks in advance.

Answer 1

You can use ROW_NUMBER to give each record a rownumber (resetting to 0 for each MeterTags.Name) then just select the first for each name:

WITH CTE AS
(   SELECT  DataLog.Timestamp, 
            MeterTags.Name, 
            DataLog.Value,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY MeterTags.Name 
                                        ORDER BY DataLog.TimeStamp DESC)
    FROM    Meters
            INNER JOIN MeterTags
                ON Meters.MeterId = MeterTags.MeterId
            INNER JOIN DataLog
                ON MeterTags.MeterTagId = DataLog.MeterTagId
    WHERE   Meters.MeterTypeId = 8
)
SELECT  CTE.Timestamp,
        CTE.Name,
        CTE.Value
FROM    CTE
WHERE   CTE.RowNumber = 1;

Another solution is to use the TOP 1 inside an APPLY:

SELECT  DataLog.Timestamp, 
        MeterTags.Name, 
        DataLog.Value
FROM    Meters
        INNER JOIN MeterTags
            ON Meters.MeterId = MeterTags.MeterId
        CROSS APPLY
        (   SELECT  TOP 1 TimeStamp, Value
            FROM    DataLog
            WHERE   MeterTags.MeterTagId = DataLog.MeterTagId
            ORDER BY TimeStamp DESC
        ) DataLog
WHERE   Meters.MeterTypeId = 8;