RANK

Syntax

RANK() OVER ([query_partition_clause] order_by_clause)

Purpose

The function assigns a rank (ranking) to each row in the query result set based on the column specified in the order_by_clause.

The ranks start from 1 in each partition and increment by 1. If two rows have the same rank, the next return value skips the rank. For example, if the rank of two rows is 1, the rank of the next row is 3.

• query_partition_clause: specifies the grouping (partitioning) for the window, that is, the rows within each group are ranked.
• order_by_clause: specifies the sorting order of rows within each partition.

Examples

1. Create a table named employees.

   CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
   

2. Insert test data into the employees table.

   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');
   

3. Rank the rows in the window based on the salary column in ascending order for each group of rows based on the job_id column.

   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