RANK

Syntax

RANK() OVER ([ query_partition_clause ] order_by_clause)

Purpose

RANK() returns the rank of each row in the result set that is sorted by the column specified by order_by_clause.

In each partition, the rank values increase starting from 1, with gaps. For example, if two rows have the same rank value 1, RANK() returns 3 instead of 2 for the next row.

Exampls

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. Partition the data by job_id, sort the data by salary, and query the rank of each row in the window.

   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