Syntax
RANK() OVER ([ query_partition_clause ] order_by_clause
Purpose
RANK() returns the rank of data rows by the column that is specified in order_by_clause.
If rows of the same rank exist, this function generates nonconsecutive ranks.
Examples
obclient> CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
INSERT INTO employees VALUES('IRIS', 11000, 'ENGINEERING');
INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
COMMIT;
Query OK, 1 row affected
obclient> SELECT last_name, RANK() OVER(PARTITION BY job_id ORDER BY salary) rank, DENSE_RANK() OVER(PARTITION BY job_id ORDER BY salary) dense_rank, PERCENT_RANK() OVER(PARTITION BY job_id ORDER BY salary) percent_rank FROM employees;
+-----------+------+------------+----------------------------------+
| last_name | rank | dense_rank | percent_rank |
+-----------+------+------------+----------------------------------+
| JIM | 1 | 1 | 0.000000000000000000000000000000 |
| TOM | 1 | 1 | 0.000000000000000000000000000000 |
| IRIS | 1 | 1 | 0.000000000000000000000000000000 |
| MIKE | 3 | 2 | 0.666666666666666666666666666667 |
| LILY | 4 | 3 | 1.000000000000000000000000000000 |
+-----------+------+------------+----------------------------------+
5 rows in set