CODEWITHSUNDEEP
sqlsql-serversql-server-2014dense-rank
Behnam
Behnam

Reputation: 1063

How to use dense_rank() to achieve real ranking

select 
    DENSE_RANK() over (order by score desc) as Rank , 
    username , 
    score 
from users

I tried above query and result was as below : 

Rank    Username    score
-----   --------    -----
1       A           3500
1       B           3500
2       C           3000
2       D           3000
2       E           3000
3       F           2000

whereas my required result is : 

Rank    Username    score
-----   --------    -----
1       A           3500
1       B           3500
3       C           3000
3       D           3000
3       E           3000
6       F           2000

how should i edit my query to get my required result?

Upvotes: 1

Views: 168

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

Use Rank() instead of Dense_Rank() will solve your problem.

SELECT 
    RANK() OVER (ORDER BY Score DESC) AS [Rank], 
    Username, 
    Score 
FROM Users

Working execution with the given sample data:

DECLARE @Users TABLE (Username VARCHAR (2), Score INT)

INSERT INTO @Users (Username, Score)
VALUES
('A', 3500),
('B', 3500),
('C', 3000),
('D', 3000),
('E', 3000),
('F', 2000)

SELECT 
    RANK() OVER (ORDER BY Score DESC) AS [Rank], 
    Username, 
    Score 
FROM @Users

So the output will be:

Rank UserName   Score
---- --------  ------
1       A       3500
1       B       3500
3       C       3000
3       D       3000
3       E       3000
6       F       2000

Upvotes: 1

Related Questions