Select all data group by 1 column and functioned by another I have the following table in SQL Server 2012 :

ID int
priority int
type int 
other data columns


I want to query the table for the following result :


grouped by 'type' 
get the id of top 1 max priority from all the grouped rows
select all rows from the id's selected in the last step


Example :

ID      Prioroty      Type           Name
1         5            3               foo1
2         4            3               foo2
3         0            4               foo3
4         0            4               foo4
5         2            64              foo5


will give the following result :

ID      Prioroty      Type           Name
1         5            3               foo1
3         0            4               foo3
5         2            64              foo5
;with cte as
(
    select
        ID,
        Priority,
        Type,
        Name,
        row_num = 
            row_number() over (partition by Type order by Priority desc)
    from dbo.your_table
)
select ID, Priority, Type, Name
from cte
where row_num = 1;