Search code examples
sqlsql-serversql-server-2008-r2database-administration

select top N 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.

****************EDIT**************************

Answer by 'Felix Pamittan' worked well but for 'N' just change it to

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 >= N

He had mentioned this in comment thought to add it to questions so that no body miss it.


Solution

  • 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