Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
;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;
share|improve this answer
    
@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 at 16:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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