RANK

Declaration

RANK() OVER ([query_partition_clause] order_by_clause)

Description

This function assigns a rank to each row in the query result set, based on the columns specified in the order_by_clause.

Within each partition, the rank starts at 1 and increments sequentially. If multiple rows share the same rank, the next rank will skip the next number. For example, if two rows have a rank of 1, the next row will have a rank of 3.

• query_partition_clause: Defines the grouping (partitioning) for the window, meaning that rows within each group will be ranked.
• order_by_clause: Defines 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. Group by job_id and sort the rows in ascending order of salary, then rank each row within the window.

   SELECT last_name, RANK() OVER(PARTITION BY job_id ORDER BY salary) RANK FROM employees;

   The result is as follows:

   +-----------+------+
   | last_name | RANK |
   +-----------+------+
   | TOM       |    1 |
   | IRIS      |    1 |
   | MIKE      |    3 |
   | LILY      |    4 |
   | JIM       |    1 |
   +-----------+------+
   5 rows in set (0.001 sec)