RANK
On this page
Returns the rank of the current row within the partition as specified by the order by clause.
If two or more rows tie they have the same ranking.
Syntax
RANK () OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]
)Return Type
An integer
Example
The following example demonstrates how the partition clause groups results and then rank is applied to those groups.
CREATE TABLE rank_t (a int, b int);INSERT INTO rank_t VALUES(1,1), (1,2), (3,2), (3,3), (3,-4);
SELECT a,b, RANK() OVER (PARTITION BY a ORDER BY b) FROM rank_t;
+------+------+-----------------------------------------+
| a | b | RANK() OVER (PARTITION BY a ORDER BY b) |
+------+------+-----------------------------------------+
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 3 | -4 | 1 |
| 3 | 2 | 2 |
| 3 | 3 | 3 |
+------+------+-----------------------------------------+
5 rows in set (0.00 sec)Last modified: February 16, 2023