Articles Cookbook
Back to list
Standard SQL

How to Add Ranking Positions to Rows with DENSE_RANK() in SQL

Database:

Operators:

DENSE_RANK, OVER, ORDER BY, ASC, DESC

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;

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:

go to top