Skip to content

DENSE_RANK()

Function Description

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

The DENSE_RANK() function handles draws (i.e. two or more rows have the same value) in a slightly different way than the RANK() function. In the event of a draw, DENSE_RANK() gives the same ranking to all rows with the same value, but no ranking immediately following it will be skipped. For example, if two rows get ranking 1, the next row will get ranking 2, not 3.

Function Syntax

> DENSE_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 table named 'SalesTable' which has 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 ('Department'), an employee name ('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, return the employee's name, their sales, and their sales ranking (using the 'DENSE_RANK()' function)
-- In this query, the 'DENSE_RANK()' function ranks all employees based on descending order of sales (specified by 'ORDER BY Sales DESC')
-- If multiple employees have the same sales, they will get the same ranking and the next sales ranking will not be skipped. So, if two employees have first sales, then the next employee ranks second, not third.
mysql>SELECT
  Employee,
  Sales,
  DENSE_RANK() OVER(ORDER BY Sales DESC) FROM
  SalesTable;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| employee | sales | density_rank() over (order by sales desc) |
+------------------------------------------------------------------------------------------------------------------------------
| Jane | 1200 | 1 |
| Bob | 1100 | 2 |
| John | 1000 | 3 |
| Alex | 900 | 4 |
| Charlie | 850 | 5 |
| Alice | 800 | 6 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 rows in set (0.01 sec)