CODEWITHSUNDEEP
sql-serverdatabase
Nick Rucci
Nick Rucci

Reputation: 1058

Mark duplicates when using Rank()

I am getting a ranked list exactly how I want to using:

SELECT RANK() OVER(ORDER BY score) as rank, name, score FROM players

Is there a way to mark duplicate ranks in SQL?

For example, from this:

rank  name  score
----  ----  -----
 1     bob    98
 2     tom    96
 2     guy    96
 4     lou    92

to this

rank  name  score
----  ----  -----
 1     bob    98
 T2    tom    96
 T2    guy    96
 4     lou    92

Upvotes: 0

Views: 197

Answers (2)

Jens.Huehn_at_SlideFab.com
Jens.Huehn_at_SlideFab.com

Reputation: 747

The following will mark all duplicate ranks. It checks whether the previous or subsequent entry has the same value.

SELECT RANK() OVER(ORDER BY score) as rank, name, score,
  IIF(LAG(score,1) OVER (ORDER BY score) = score OR LEAD(score,1) OVER (ORDER BY score) = score,1,0) as IsDuplicate
FROM players

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89071

You could do something like

with q as
(
SELECT RANK() OVER(ORDER BY score) as rank, 
name, score 
FROM players
)
select *, count(*) over (partition by rank) players_at_rank
from q

rank                 name                 score       players_at_rank
-------------------- -------------------- ----------- ---------------
1                    alex                 2           1
2                    fred                 5           2
2                    joe                  5           2
4                    sue                  9           1

(4 row(s) affected)

Upvotes: 1

Related Questions