1

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
3

You can use ROW_NUMBER();

WITH CTE AS
(   SELECT  ID, 
            Prioroty, 
            Type, 
            Name, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Prioroty DESC)
    FROM    T
)
SELECT  ID, Prioroty, Type, Name
FROM    CTE
WHERE   RowNum = 1;

Example on SQL Fiddle

3
;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;
  • @AaronBertrand Ha!!! Thanks for the edit. That's what I get for typing out a query in a browser instead of SSMS. :-) – Thomas Stringer Jan 9 '14 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.