Microsoft SQL Server : return only the rows with the most recent date for each unique ID

Question

I've got some data that has a DeviceID column, a scan time column and some other columns.

For each of the deviceIDs, I want to return only the most recent row based on the scan time.

I am trying to create this query so that I can use it as a view and report on the data.

The database is a Microsoft SQL Server database and I'm running the query from SQL Server 2014 Management Studio.

The closest I've gotten to getting this to work is this :

SELECT
    DeviceID,
    AVSolutionName, 
    DefinitionsUpToDate,
    ScanningEnabled,
    Expired, 
    ScanTime
FROM 
    dbo.fact_AVSecurity
WHERE 
    (ScanTime IN (SELECT DISTINCT MAX(ScanTime) AS LastScan
                  FROM dbo.fact_AVSecurity AS Avs
                  GROUP BY DeviceID))

Unfortunately this is returning multiple values for the same ID.

ScanTime        ScanningEnabled Expired    DeviceID DefinitionsUpToDate   AVSolutionName
10/12/2018 10:13    TRUE             FALSE      15994             TRUE    Webroot SecureAnywhere
4/12/2018 14:30     TRUE             TRUE       15994             TRUE    Webroot SecureAnywhere

What I'd like returned is just that first most recent row:

ScanTime        ScanningEnabled Expired    DeviceID DefinitionsUpToDate   AVSolutionName
10/12/2018 10:13    TRUE             FALSE      15994             TRUE       Webroot SecureAnywhere

I've tried different approaches like : SQL - Returning only the most recent row

But can't seem to get them working. I'm not sure if it's something I'm doing wrong or if the specific brand of SQL I'm using doesn't do the "top 1" thing.

Is there a way to do what I'm after? How close am I with what I have?

score 2 · Answer 1 · answered Dec 10 '18 at 22:22

use a window function with a CTE?

With CTE AS (
SELECT t.DeviceID
     , t.AVSolutionName
     , t.DefinitionsUpToDate
     , t.ScanningEnabled
     , t.Expired
     , t.ScanTime
     , Row_Number() over (partition by DeviceID order by scanTime Desc) RN
FROM dbo.fact_AVSecurity t)
SELECT * 
FROM CTE 
WHERE RN=1

score 1 · Answer 2 · answered Dec 10 '18 at 22:19

Check this:

SELECT t.DeviceID, t.AVSolutionName, t.DefinitionsUpToDate, t.ScanningEnabled, t.Expired, t.ScanTime
FROM dbo.fact_AVSecurity AS t
WHERE t.ScanTime = (SELECT MAX(Avs.ScanTime) FROM dbo.fact_AVSecurity AS Avs WHERE Avs.DeviceID = t.DeviceID)

for each DeviceID fetches the row that has ScanTime = MAX(ScanTime)

score 1 · Accepted Answer · answered Dec 10 '18 at 22:19

You are close to the solution. You just need a few changes in your correlated subquery :

add a WHERE condition in your subquery that limits the search to the current DeviceID
No need to use an IN clause to match the subquery, equality should be fine as only one record is expected anyway
No need to use DISTINCT as you are already using a GROUP BY

Query :

SELECT 
    t.DeviceID, 
    t.AVSolutionName,
    t.DefinitionsUpToDate, 
    t.ScanningEnabled,
    t.Expired,
    t.ScanTime
FROM dbo.fact_AVSecurity AS t
WHERE t.ScanTime =
    (SELECT MAX(ScanTime) AS LastScan
      FROM dbo.fact_AVSecurity AS Avs
      WHERE deviceID = t.deviceID
      GROUP BY DeviceID
    )

score 1 · Answer 4 · answered Dec 10 '18 at 22:23

If you have an auto-increment column on your table (you generally should have one on every table), use that instead of timestamps, since SQL Server DateTime type only has a resolution of 1/300^th of a second and should not be assumed to be a unique timestamp.

SELECT X.LastEntryID, DeviceID = Y.ID, ...
FROM
    (
        SELECT LastEntryID = MAX(ID)--latest entry for the device
        FROM dbo.fact_AVSecurity
        GROUP BY DeviceID--you don't even need to return DeviceID since ID is auto-increment and thus unique in the table
    ) AS X
    INNER JOIN dbo.fact_AVSecurity AS Y ON
        Y.ID = X.LastEntryID

This presumes you don't backdate your data or populate using IDENTITY_INSERT

score 1 · Answer 5 · answered Dec 10 '18 at 22:44

Just one final option because I didn't see it mentioned

You can use the WITH TIES in concert with Row_Number()

That said, xQbert's solution (+1) would be more performant, especially with larger tables

Example

SELECT Top 1 with ties *
 FROM  dbo.fact_AVSecurity 
 Order By Row_Number() over (partition by DeviceID order by scanTime Desc)

Microsoft SQL Server : return only the rows with the most recent date for each unique ID

5 Answers5