Search code examples
sqlsql-servert-sqlsql-server-2012greatest-n-per-group

Getting all values from table after grouping by id

I have a table that contains items with three values Id, RevisionId and Data like this.

| Id | RevisionId | Data   |
| 0  | 0          | Value1 |
| 0  | 1          | Value2 |
| 1  | 0          | Value1 |
| 2  | 0          | Value1 |
| 2  | 1          | Value2 |
| 2  | 3          | Value3 |

If I want only the Id with the highest RevisionId for each I can make an SQL statement like this.

SELECT Id, MAX(RevisionId) AS RevisionId FROM RevisionTable GROUP BY Id

But I don't know how to get the Data value connected to that max RevisionId for each Id.

Solution

  • You can use row_number():

    select *
from (
    select t.*, row_number() over(partition by id order by revisionid desc) rn
    from mytable t
) t
where rn = 1

    top(1) with ties also comes to mind:

    select top (1) with ties t.*
from mytable t
order by row_number() over(partition by id order by revisionid desc) rn