How to Add Ranking Positions of Rows in SQL with RANK()

Problem:

You want to rank records by skipping ranking positions after rows that have the same rank.

Example:

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

Let’s display all users’ names and their scores sorted in descending order and ranked by score.

Solution 1:

SELECT RANK() OVER(ORDER BY score DESC) AS rank_place,
  user_name,
  score
FROM championship;

This query returns the following ranking:

Notice that the rank number is from 1 to 5, sorted by score in descending order.

Discussion:

Use RANK if you want to skip ranking positions after rows with the same rank. In our example, Mary, Alan, and Chris all had the same ranking of 2. The next eligible position is 5 (calculated from 2 + 3 rows with the same rank).