top of page
CerebroSQL

MySQL: 

RANK

RANK() over_clause

Returns the rank of the current row within its partition, with gaps.
Peers are considered ties and receive the same rank. This function does
not assign consecutive ranks to peer groups if groups of size greater
than one exist; the result is noncontiguous rank numbers.

This function should be used with ORDER BY to sort partition rows into
the desired order. Without ORDER BY, all rows are peers.

over_clause is as described in
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

Example

mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+------+------------+
| val | row_number | rank | dense_rank |
+------+------------+------+------------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 1 | 1 |
| 2 | 3 | 3 | 2 |
| 3 | 4 | 4 | 3 |
| 3 | 5 | 4 | 3 |
| 3 | 6 | 4 | 3 |
| 4 | 7 | 7 | 4 |
| 4 | 8 | 7 | 4 |
| 5 | 9 | 9 | 5 |
+------+------------+------+------------+

bottom of page