RANK

Syntax

RANK() OVER ([query_partition_clause] order_by_clause)

Purpose

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. For rows with the same rank, the next rank value 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) of 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 the employees table.

   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 rank rows in ascending order of salary within each group.

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

   The result set is as follows:

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