CODEWITHSUNDEEP
sqlsql-servert-sqljoingreatest-n-per-group
gluetornado
gluetornado

Reputation: 45

Select unique value with most recent date, grouping by another value

I have two joined tables, one lists devices IDs and names the other IDs and date of the most recent inventory collection. Using below query I will see something like that:

SELECT d.DeviceName, d.DeviceID, r.InvDate FROM Device d
JOIN Report r on d.DeviceID = r.DeviceID

as you can see some names can be duplicated.

How can I extract list of unique Names, (EDIT: together with their IDs) and the most recent inventory date? In some perfect world I'd do below

SELECT d.DeviceID, MAX(r.InvDate) FROM Device d
JOIN Report r on d.DeviceID = r.DeviceID
GROUP BY d.DeviceName

Upvotes: 0

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I would recommend apply for this:

SELECT d.DeviceID, d.DeviceName, r.*
FROM Device d OUTER APPLY
     (SELECT TOP (1) r.*
      FROM report r
      WHERE d.DeviceID = r.DeviceID
      ORDER BY r.InvDate DESC
     ) r;

Note only is this more efficient than using ROW_NUMBER() (in general), but it is a nice introduce to lateral joins.

Upvotes: 0

zealous
zealous

Reputation: 7503

You can do this by row_number() as well.

select
    DeviceName,
    DeviceID,
    InvDate
from
(
    select
        DeviceName,
        d.DeviceID,
        InvDate,
        row_number() over (partition by DeviceName order by InvDate desc) as rnk
    from device
    join report r
    on d.DeviceID = r.DeviceID
) vals
where rnk = 1
order by    
    DeviceID

Upvotes: 2

Related Questions