How to add ranking positions of rows with RANK in SQL

Problem

You want to add ranking positions to rows in your SQL table using the RANK() function.

Sample data

Let's assume we have a Students table with the following data:

MySQL Solution

SELECT ID, Name, Marks,
RANK() OVER(ORDER BY Marks DESC) RankPosition
FROM Students;

MySQL Explanation

In MySQL, the RANK() function is a window function that assigns a unique rank to each row within a result set. The rows with equal values receive the same rank, and the next rank is skipped. In this case, the RANK() function is used to assign a rank to each student based on their marks, with the student with the highest marks receiving a rank of 1. The OVER(ORDER BY Marks DESC) clause specifies that the ranking should be done in descending order of marks.

PostgreSQL Solution

SELECT ID, Name, Marks,
RANK() OVER(ORDER BY Marks DESC) RankPosition
FROM Students;

PostgreSQL Explanation

The RANK() function in PostgreSQL works the same way as in MySQL. It is a window function that assigns a unique rank to each distinct row within a result set. The rows with equal values receive the same rank, and the next rank is skipped. In this case, the RANK() function is used to assign a rank to each student based on their marks, with the student with the highest marks receiving a rank of 1. The OVER(ORDER BY Marks DESC) clause specifies that the ranking should be done in descending order of marks.