RANK()

Description

RANK() provides a unique rank for each row in the dataset. RANK() first sorts the dataset according to the columns specified in the ORDER BY clause, then assigns each row a unique rank.

The RANK() function has a unique behavior when it handles the case of equal values ​​(i.e., tie). When two or more rows have the same value, they get the same rank. It will then skip the rank or ranks that follow it. For example, if two rows rank 1, the next row gets rank 3, not 2.

Syntax

  1. > RANK() OVER (
  2.     [PARTITION BY column_1, column_2, ... ]
  3.     ORDER BY column_3, column_4, ...
  4. )
  • The PARTITION BY clause is optional and divides the dataset into partitions; the rank is computed individually inside each partition.
  • The ORDER BY clause defines how the dataset is sorted, i.e., according to which column or columns to sort. You can specify ascending (ASC) or descending (DESC) sorting.

Examples

  1. -- Create a new table, 'SalesTable' with three fields: 'Department', 'Employee', and 'Sales'
  2. CREATE TABLE SalesTable (
  3.   Department VARCHAR(50),
  4.   Employee VARCHAR(50),
  5.   Sales INT
  6. );
  8. -- Insert data into the 'SalesTable' table; each row contains a department (Department), an employee (Employee), and their sales (Sales)
  9. INSERT INTO SalesTable (Department, Employee, Sales) VALUES
  10. ('Marketing', 'John', 1000),
  11. ('Marketing', 'Jane', 1200),
  12. ('Sales', 'Alex', 900),
  13. ('Sales', 'Bob', 1100),
  14. ('HR', 'Alice', 800),
  15. ('HR', 'Charlie', 850);
  17. -- Query the 'SalesTable' table and return each employee in each department, their sales, and the total sales of their department (DepartmentSales)
  18. -- will also return each employee's sales rank in their department (SalesRank)
  19. -- For the total sales, use the window function SUM(), and use 'OVER(PARTITION BY Department)' to calculate each department separately
  20. -- For sales ranking, use the window function RANK(), and use 'OVER(PARTITION BY Department ORDER BY Sales DESC)' to rank employees in each department in descending order by sales
  21. -- In the RANK() function, if two employees have the same sales, they get the same rank, and the next sales rank is skipped. For example, if two employees are number one in sales, the next sales rank is number three, not number two.
  22. mysql> SELECT
  23.   Department,
  24.   Employee,
  25.   Sales,
  26.   SUM(Sales) OVER(PARTITION BY Department) AS DepartmentSales,
  27.   RANK() OVER(PARTITION BY Department ORDER BY Sales DESC) AS SalesRank
  28. FROM
  29.   SalesTable;
  30. +------------+----------+-------+-----------------+-----------+
  31. | department | employee | sales | DepartmentSales | SalesRank |
  32. +------------+----------+-------+-----------------+-----------+
  33. | HR         | Charlie  |   850 |            1650 |         1 |
  34. | HR         | Alice    |   800 |            1650 |         2 |
  35. | Marketing  | Jane     |  1200 |            2200 |         1 |
  36. | Marketing  | John     |  1000 |            2200 |         2 |
  37. | Sales      | Bob      |  1100 |            2000 |         1 |
  38. | Sales      | Alex     |   900 |            2000 |         2 |
  39. +------------+----------+-------+-----------------+-----------+
  40. 6 rows in set (0.01 sec)