CODEWITHSUNDEEP
sqlsql-server
Michael Edwards
Michael Edwards

Reputation: 6528

SQL Server select first instance of ranked data

I have a query that creates a result set like this:

Rank   Name
1      Fred
1      John
2      Mary
2      Fred
2      Betty
3      John
4      Betty
4      Frank

I need to then select the lowest rank for each name, e.g.:

Rank   Name
1      Fred
1      John
2      Mary
2      Betty
4      Frank

Can this be done within TSQL?

Upvotes: 3

Views: 4806

Answers (3)

StuartLC
StuartLC

Reputation: 107317

As Paul + Kevin have pointed out, simple cases of returning a value from an aggregate can be extracted using MIN / MAX etc (just note that RANK is a reserved word)

In a more general / complicated case, e.g. where you need to find the second / Nth highest rank, you can use PARTITIONs with ROW_NUMBER() to do ranking and then filter by the rank.

SELECT [Rank], [Name]
FROM
(
    SELECT [RANK], [Name], 
           ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Rank]) as [RowRank]
    FROM [MyTable]
) AS [MyTableReRanked]
WHERE [RowRank] = @N
ORDER BY [Rank];

Upvotes: 3

paul
paul

Reputation: 22001

yes

select name, min(rank)
from nameTable
group by name

Upvotes: 4

Kevin Aenmey
Kevin Aenmey

Reputation: 13419

SELECT MIN(Rank) AS Rank, Name
FROM TableName
GROUP BY Name

Upvotes: 6

Related Questions