RANK() vs DENSE_RANK() vs ROW_NUMBER() in SQL
In the world of SQL, a window function is a powerful construct that allows users to segment and manipulate data in precise ways. By grouping data based on specific columns and sorting criteria, window functions enable advanced computations within partitions.
In this comprehensive tutorial, we will explore three of the most frequently used window functions: ROW_NUMBER(), DENSE_RANK(), and RANK(). Whether you're a seasoned SQL veteran or just getting started, this guide will equip you with the knowledge and practical examples you need to master these essential tools.
All code snippets included have been tested on a MySQL database, and these functions should run for pretty much any SQL flavour with minimal or no modifications required.
Subscribe to Data Pipeline, a newsletter dedicated to Data Engineering
First, let's create an example table that we will be referencing throughout the tutorial in order to demonstrate a few different concepts.
Sql">CREATE TABLE employees (
id integer,
first_name varchar(20),
last_name varchar(20),
position varchar(20),
salary varchar(20)
)
INSERT INTO employees VALUES
(1, 'Andrew', 'Brown', 'Manager', 100000),
(2, 'Maria', 'Johnson', 'Manager', 105000),
(3, 'John', 'Anderson', 'Senior Manager', 130000),
(4, 'Alex', 'Purple', 'Associate', 50000),
(5, 'George', 'Bull', 'Senior Associate', 65000),
(6, 'Jess', 'Fridman', 'Associate', 48000),
(7, 'Marion', 'White', 'Senior Associate', 65000),
(8, 'Andreea', 'Berton', 'Manager', 102000),
(9, 'Bob', 'Johanson', 'Associate', 45000),
(10, 'Georgia', 'Hoffman', 'Senior Associate', 66000),
(11, 'Johan', 'Peterson', 'Senior Associate', 58000);And here's how our sample table looks like:
SELECT * FROM employees;
| id | first_name | last_name | position | salary |
| --- | ---------- | --------- | ---------------- | ------ |
| 1 | Andrew | Brown | Manager | 100000 |
| 2 | Maria | Johnson | Manager | 105000 |
| 3 | John | Anderson | Senior Manager | 130000 |
| 4 | Alex | Purple | Associate | 50000 |
| 5 | George | Bull | Senior Associate | 65000 |
| 6 | Jess | Fridman | Associate | 48000 |
| 7 | Marion | White | Senior Associate | 65000 |
| 8 | Andreea | Berton | Manager | 102000 |
| 9 | Bob | Johanson | Associate | 45000 |
| 10 | Georgia | Hoffman | Senior Associate | 66000 |
| 11 | Johan | Peterson | Senior Associate | 58000 |What is a window function?
A window function is used in SQL to perform calculations over a group of rows and returns one value per row. Note the difference from an aggregate function that is used to return just a single value for each group. Window functions are usually used to compute cumulative sums, rankings as well as moving averages.
A valid syntax for window functions should include
- the
OVERclause is specified after the function and is used to reference the window - and the window specification that specifies the way the rows should be grouped (this specification can include
PARTITION BYand/orORDER BYclauses)
The RANK() window function
The RANK() function returns the rank for each row within the specified group or partition(s). In case more than one rows have the same value within the partition, then all of them will be assigned the same ranking. In this case, a gap will be created in the rankings given that subsequent rows will be assigned the number that corresponds to their actual position in the rank (and not the next available rank).
Now let's consider we would like to create a ranking for the records in employees such that employees serving in the same position are ranked based on their earnings. This can be achieved using the following query:
SELECT
*,
RANK() OVER (PARTITION BY position ORDER BY salary DESC) AS emp_pos_rank
FROM
employees;
| id | first_name | last_name | position | salary | emp_pos_rank |
| --- | ---------- | --------- | ---------------- | ------ | ------------ |
| 4 | Alex | Purple | Associate | 50000 | 1 |
| 6 | Jess | Fridman | Associate | 48000 | 2 |
| 9 | Bob | Johanson | Associate | 45000 | 3 |
| 2 | Maria | Johnson | Manager | 105000 | 1 |
| 8 | Andreea | Berton | Manager | 102000 | 2 |
| 1 | Andrew | Brown | Manager | 100000 | 3 |
| 10 | Georgia | Hoffman | Senior Associate | 66000 | 1 |
| 5 | George | Bull | Senior Associate | 65000 | 2 |
| 7 | Marion | White | Senior Associate | 65000 | 2 |
| 11 | Johan | Peterson | Senior Associate | 58000 | 4 |
| 3 | John | Anderson | Senior Manager | 130000 | 1 |Note the gap created in the rankings for Senior Associate position. Two employees were assigned the second rank which means the record following these two records will be assigned a rank of 4 (and not 3).
The DENSE_RANK() window function
The DENSE_RANK() function returns the rank for each row within the specified group or partition(s). In contrast to RANK(), the DENSE_RANK() will have no gaps:
SELECT
*,
DENSE_RANK() OVER (PARTITION BY position ORDER BY salary DESC) AS emp_pos_rank
FROM
employees;
| id | first_name | last_name | position | salary | emp_pos_rank |
| --- | ---------- | --------- | ---------------- | ------ | ------------ |
| 4 | Alex | Purple | Associate | 50000 | 1 |
| 6 | Jess | Fridman | Associate | 48000 | 2 |
| 9 | Bob | Johanson | Associate | 45000 | 3 |
| 2 | Maria | Johnson | Manager | 105000 | 1 |
| 8 | Andreea | Berton | Manager | 102000 | 2 |
| 1 | Andrew | Brown | Manager | 100000 | 3 |
| 10 | Georgia | Hoffman | Senior Associate | 66000 | 1 |
| 5 | George | Bull | Senior Associate | 65000 | 2 |
| 7 | Marion | White | Senior Associate | 65000 | 2 |
| 11 | Johan | Peterson | Senior Associate | 58000 | 3 |
| 3 | John | Anderson | Senior Manager | 130000 | 1 |Note how the 4th Senior Associate will now get a rank equal to 3, given that two other emloyees are sharing the second rank and no gaps should be created by DENSE_RANK window function.
The ROW_NUMBER() window function
Finally, the ROW_NUMBER window function will assign a number to every row starting from index 1 for the first row within each partition.
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY position ORDER BY salary DESC) AS emp_pos_rank
FROM
employees;
| id | first_name | last_name | position | salary | emp_pos_rank |
| --- | ---------- | --------- | ---------------- | ------ | ------------ |
| 4 | Alex | Purple | Associate | 50000 | 1 |
| 6 | Jess | Fridman | Associate | 48000 | 2 |
| 9 | Bob | Johanson | Associate | 45000 | 3 |
| 2 | Maria | Johnson | Manager | 105000 | 1 |
| 8 | Andreea | Berton | Manager | 102000 | 2 |
| 1 | Andrew | Brown | Manager | 100000 | 3 |
| 10 | Georgia | Hoffman | Senior Associate | 66000 | 1 |
| 5 | George | Bull | Senior Associate | 65000 | 2 |
| 7 | Marion | White | Senior Associate | 65000 | 3 |
| 11 | Johan | Peterson | Senior Associate | 58000 | 4 |
| 3 | John | Anderson | Senior Manager | 130000 | 1 |Final Thoughts
In conclusion, window functions are a powerful feature of SQL that allow for complex calculations to be performed over a specific subset of rows in a result set. They can be used to calculate running totals, rolling averages, and other metrics that require a reference to neighboring rows.
In today's article we discussed about the difference between RANK(), DENSE_RANK() and ROW_NUMBER() functions. By understanding the differences between these types of window functions, you can choose the most appropriate one for your specific use case and optimize the performance of your SQL queries.
