Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function
RANK(), DENSE_RANK(), and ROW_NUMBER() function have very similar behaviors. Here we put them together to visually and programmatically illustrate their
common attributes as well as differences.
| RANK()
The RANK() function allocates a rank (integer number) to each row within a group of a data set, with gaps, and the rank values could duplicate. |
When a student has the same mark as another student, they both receive the same rank value, but unlike DENSE_RANK() function, RANK() function does NOT assign consecutive ranks.
In Class A, both Gary and Tom are assigned the same rank value 2 as they have the same mark, but John receives rank value 4 where 3 is skipped.
SELECT StudentName,
ClassName,
StudentMark,
RANK() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;
|
| DENSE_RANK()
The DENSE_RANK() function allocates a rank (integer number) to each row within a group of a data set, without gaps, and the rank values could duplicate. |
When a student has the same mark as another student, they both receive the same rank value, but unlike RANK() function, DENSE_RANK() function assigns consecutive rank values.
In Class A, both Gary and Tom are assigned the same rank value 2 as they have the same mark, and John receives rank value 3 which is consecutively incremented from 2.
SELECT StudentName,
ClassName,
StudentMark,
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;
|
| ROW_NUMBER()
The ROW_NUMBER() function allocates a different row number (integer number, starting from 1) to each row within a group of a data set, without both gaps and duplicated values. |
When a student has the same mark as another student, each of them receives a distinct row number. Furthermore, unlike RANK() and DENSE_RANK(), ROW_NUMBER() function assigns consecutive, non-duplicated numbers.
In Class A, both Gary and Tom are assigned different numbers even though they have the same mark, and John receives number 4 which is consecutively incremented from 3.
SELECT StudentName,
ClassName,
StudentMark,
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;
|
Put all 3 functions together
In the following query, we put RANK(), DENSE_RANK(), and ROW_NUMBER() function into a single query.
Please note that we have used each function's name as alias for column names, but because they are reserved words in MySQL,
we have to surround (a.k.a. escape) them by double quotes (alternatively you can use back tick character ` instead of double quotes).
SELECT StudentName,
ClassName,
StudentMark,
RANK() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as "RANK",
DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as "DENSE_RANK",
ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as "ROW_NUMBER"
FROM exam_result;
9 records returned.
Happy Coding!
Other tutorials in this category
1. Using String Functions, Part 1
2. Using String Functions, Part 2
3. Using String Functions, Part 3
4. Using MySQL Date and Time Functions, Part 1
5. Using MySQL Date and Time Functions, Part 2
6. Using MySQL Date and Time Functions, Part 3
7. Using MySQL Control Flow Functions
8. Using MySQL Conversion Functions
9. Using MySQL Comparison Functions
10. Using Aggregate Functions in MySQL
11. MySQL RANK() function
12. MySQL DENSE_RANK() function
13. MySQL ROW_NUMBER() function
14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL
15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL
16. Use LEAST function to find the SMALLEST value from multiple arguments
17. How to make case-sensitive comparison in MySQL
18. Use GREATEST function to find the LARGEST value from multiple arguments
19. MySQL MID() function with examples
20. MySQL LOCATE() function with examples
21. MySQL SUBSTR() function with examples
22. MySQL POSITION() function with examples
23. How to use MySQL REPLACE function in SELECT and UPDATE queries
Back to Tutorial Index Page
