How to Add Rankings with DENSE_RANK() in SQL

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.

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.