Anshul Nigam Anshul Nigam - 5 months ago 13
SQL Question

select top 5 records for each entity

I have a

table
like below -




ID | Reported Date | Device_ID
-------------------------------------------
1 | 2016-03-09 09:08:32.827 | 1
2 | 2016-03-08 09:08:32.827 | 1
3 | 2016-03-08 09:08:32.827 | 1
4 | 2016-03-10 09:08:32.827 | 2
5 | 2016-03-05 09:08:32.827 | 2


Now, i want a
top 1
row
based on
date column
for each
device_ID


Expected Output




ID | Reported Date | Device_ID
-------------------------------------------
1 | 2016-03-09 09:08:32.827 | 1
4 | 2016-03-10 09:08:32.827 | 2


I am using
SQL Server 2008 R2
. i can go and write
Stored Procedure
to handle it but wanted do it with simple query.

Answer

Use ROW_NUMBER:

SELECT
    Id, [Reported Date], Device_ID
FROM (
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY Device_ID ORDER BY [ReportedDate] DESC)
    FROM tbl
)t
WHERE Rn = 1
Comments