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

 id user_name score 111 John 12 112 Mary 23 115 Lisa 45 120 Alan 23 221 Chris 23

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_place user_name score 1 Lisa 45 2 Mary 23 2 Alan 23 2 Chris 23 5 John 12

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