Articles Cookbook
Back to list
Standard SQL

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

Database:

Operators:

RANK, OVER, ORDER BY, ASC, DESC

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.

iduser_namescore
111John12
112Mary23
115Lisa45
120Alan23
221Chris23

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:

rank_placeuser_namescore
1Lisa45
2Mary23
2Alan23
2Chris23
5John12

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).

Recommended courses:

Recommended articles:

See also:

go to top