Declaration
RANK() OVER ([query_partition_clause] order_by_clause)
Description
This function assigns a rank to each row in the query result set. The columns for determining the rank are specified in the order_by_clause.
Within each partition, the rank starts at 1 and increments sequentially. For rows with the same rank, the next returned value skips over that rank. For example, if two rows have a rank of 1, the rank of the next row will skip 2 and be assigned as 3.
query_partition_clause: Defines the grouping (partitioning) for the window, meaning rows within each group will be ranked.order_by_clause: defines the sorting order of rows within each partition.
Examples
Create the
employeestable.CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));Insert test data into the
employeestable.INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING'); INSERT INTO employees VALUES('IRIS', 11000, 'ENGINEERING'); INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING'); INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING'); INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');The rank of each row in the sorting column within the window, grouped by
job_idand sorted in ascending order of salary.SELECT last_name, RANK() OVER(PARTITION BY job_id ORDER BY salary) RANK FROM employees;The return result is as follows:
+-----------+------+ | last_name | RANK | +-----------+------+ | TOM | 1 | | IRIS | 1 | | MIKE | 3 | | LILY | 4 | | JIM | 1 | +-----------+------+ 5 rows in set