Back to cookbooks list Articles Cookbook

How to Add Rankings with DENSE_RANK() in SQL

Database:

Operators:

Table of Contents

Problem:

You want to add a ranking position to rows consecutively, even if the rows have the same values.

Example:

Our database has a table named competition with data in the following columns: id (primary key), first_name, last_name, and score.

idfirst_namelast_namescore
11JohnThomas345
14MaryJohnson222
16LisaBrown154
23AlanBlake222
32ChrisTaylor154

Let’s display all details about students: their last name, first name, and scores sorted by score in descending order. We don’t want to skip position numbers if some scores are the same for more than one student.

Solution 1:

SELECT DENSE_RANK() OVER(ORDER BY score DESC) AS d_rank,
  first_name,
  last_name,
  score
FROM competition;
The query returns the following ranking:
d_rank first_name last_name score
1 John Thomas 345
2 Mary Johnson 222
2 Alan Blake 222
3 Lisa Brown 154
3 Chris Taylor 154

Notice that the rank ranges from 1 to 3.

Discussion:

Use DENSE_RANK if you don’t want to skip ranking positions after rows with the same rank. In our example, even though Mary and Alan have the same rank of 2, the position for Lisa is 3.

Recommended courses:

Recommended articles:

See also: