Skip to content

RANK()

Function Description

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

The RANK() function has a special behavior when dealing with the same value (i.e., tie) situation: when two or more rows have the same value, they will get the same ranking. It will then skip one or more rankings that follow. For example, if two rows get ranking 1, the next row will get ranking 3 instead of 2.

Function Syntax

> RANK() OVER (
    [PARTITION BY column_1, column_2, ... ]
    ORDER BY column_3, column_4, ...
)
  • The PARTITION BY clause is optional, which divides the dataset into multiple partitions, and ranks are calculated separately within each partition.
  • The ORDER BY clause defines how the dataset is sorted, that is, which column or columns are sorted according to. You can specify ascending (ASC) or descending (DESC) sorting.

Example

-- Create a new table 'SalesTable' with three fields: 'Department', 'Employee' and 'Sales'
CREATE TABLE SalesTable (
  Department VARCHAR(50),
  Employee VARCHAR(50),
  Sales INT
);

-- Insert data into the 'SalesTable' table, each row contains a department, an employee, and their sales (Sales)
INSERT INTO SalesTable (Department, Employee, Sales) VALUES
('Marketing', 'John', 1000),
('Marketing', 'Jane', 1200),
('Sales', 'Alex', 900),
('Sales', 'Bob', 1100),
('HR', 'Alice', 800),
('HR', 'Charlie', 850);

-- Query the 'SalesTable' table and return each employee in each department, their sales, and the total sales of their department (DepartmentSales)
-- It will also return the sales rankings for each employee in their department (SalesRank)
-- For total sales, use the window function SUM() and calculate each department separately with 'OVER(PARTITION BY Department)'
-- 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 based on sales
-- In the RANK() function, if two employees have the same sales, they will get the same ranking and the ranking of the next sales will be skipped. For example, if two employees have first sales, the next sales ranking is third, not second.
mysql>SELECT
  Department,
  Employee,
  Sales,
  SUM(Sales) OVER(PARTITION BY Department) AS DepartmentSales,
  RANK() OVER(PARTITION BY Department ORDER BY Sales DESC) AS SalesRank
FROM
  SalesTable;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| department | employee | sales | DepartmentSales | SalesRank |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| HR | Charlie | 850 | 1650 | 1 |
| HR | Alice | 800 | 1650 | 2 |
| Marketing | Jane | 1200 | 2200 | 1 |
| Marketing | John | 1000 | 2200 | 2 |
| Sales | Bob | 1100 | 2000 | 1 |
| Sales | Alex | 900 | 2000 | 2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 rows in set (0.01 sec)