Question of the day (MySQL)

Q. Explain RANK() and DENSE_RANK() function in MySQL with an example.

1 Like

The RANK() and DENSE_RANK() functions in MySQL are used to assign a rank to each row within a result set based on the values in one or more columns. Both functions are used to determine the relative position of a row in a result set.

The RANK() function assigns a unique rank to each row based on the ordering of values in the specified column(s). If two or more rows have the same value, they receive the same rank and the next rank is skipped. For example:

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM students;
This query will assign a rank to each student based on their score in descending order. If two or more students have the same score, they will receive the same rank and the next rank will be skipped. For example:

±------±------±-----+
| name | score | rank |
±------±------±-----+
| John | 95 | 1 |
| Sarah | 90 | 2 |
| Tom | 90 | 2 |
| Jane | 85 | 4 |
±------±------±-----+
In this example, John has the highest score, so he receives rank 1. Sarah and Tom have the same score, so they receive the same rank (2), and Jane has the fourth highest score, so she receives rank 4.

The DENSE_RANK() function works similarly to the RANK() function, but it does not skip any ranks if two or more rows have the same value. For example:

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;
This query will assign a rank to each student based on their score in descending order. If two or more students have the same score, they will receive the same rank, but the next rank will not be skipped. For example:

±------±------±-----+
| name | score | rank |
±------±------±-----+
| John | 95 | 1 |
| Sarah | 90 | 2 |
| Tom | 90 | 2 |
| Jane | 85 | 3 |
±------±------±-----+
In this example, John has the highest score, so he receives rank 1. Sarah and Tom have the same score, so they both receive rank 2 (without any skipped ranks), and Jane has the fourth highest score, so she receives rank 3.

2 Likes