SQL Window Functions: Understanding Row Number, Rank, and Dense Rank

In the world of SQL (Structured Query Language), various window functions play a vital role in data analysis and manipulation. Among them, “RANK,” “DENSE_RANK,” and “ROW_NUMBER” are commonly used to assign a rank or a unique number to each row in a result set based on specified criteria. In this article, we will delve into the differences between these three functions and understand their applications in SQL queries.

1. ROW_NUMBER

ROW_NUMBER is a window function that assigns a unique sequential number to each row within a result set, without any regard for the data’s order or values. The function provides an essential tool for partitioning data into subsets and ensuring each row receives a distinct identifier. The ROW_NUMBER function does not consider duplicate values and always generates consecutive numbers starting from 1.

Usage Example:

first we prepare a table named “user” which has 2 columns (name and age) and has 5 rows of data.

table user

next step is creating a query that will get 3 columns (name, age and row_num) row_num is generated using the ROW_NUMBER window function to number the row, and we will order the result ascending based on the row_num.

SELECT 
  name, 
  age, 
  ROW_NUMBER() OVER (ORDER BY age asc) as row_num
FROM user
ORDER BY row_num asc;

then the result from the query on the top will look like this, as we can see that there are 3 columns (name, age and row_num) and the data is sorted asceding based on the value of row_num.

the result table from the query

2. RANK

RANK is another window function that assigns a unique rank to each row within a result set based on a specified column’s values. It takes into account duplicate values and assigns the same rank to rows with identical values, leaving gaps in the ranking sequence. In case of a tie, the subsequent rank(s) will be skipped, and the following rank will be incremented accordingly.

Usage Example:

first we prepare a table named “user” which has 2 columns (name and age) and has 5 rows of data.

table user

next step is creating a query that will get 3 columns (name, age and rank_age) rank_age is generated using the RANK window function to give rank based on the age, and we will order the result ascending based on the rank_age.

SELECT 
  name, 
  age, 
  RANK() OVER (ORDER BY age asc) as rank_age
FROM user
ORDER BY rank_age asc;

The query’s output is a table containing three columns: name, age, and rank_age. In the rank_age column, two rows share the same rank, which is 2. However, the rank with a value of 3 is absent and is instead skipped, moving on to the value 4.

the result table from the query

3. DENSE_RANK

DENSE_RANK, like RANK, is a window function that assigns ranks to rows based on a specified column’s values. However, it differs from RANK in the way it handles duplicates. Unlike RANK, DENSE_RANK does not leave gaps in the ranking sequence and assigns the same rank to rows with identical values. This means that two identical rows will receive the same rank, and the subsequent rank will be incremented by one.

Usage Example:

first we prepare a table named “user” which has 2 columns (name and age) and has 5 rows of data.

table user

next step is creating a query that will get 3 columns (name, age and dense_rank_age), dense_rank_age is generated using the DENSE_RANK window function to give rank based on the age, and we will order the result ascending based on the rank_age.

SELECT 
  name, 
  age, 
  DENSE_RANK() OVER (ORDER BY age asc) as dense_rank_age
FROM user
ORDER BY dense_rank_age asc;

The result of the query is a table that comprises three columns: name, age, and dense_rank_age. Within the dense_rank_age column, there are two rows with an equal rank of 2. The following rank is 3, which sets the dense rank function apart from the rank function.

the result table from the query

Key Differences Between the 3 functions:

• ROW_NUMBER always generates consecutive numbers starting from 1, without considering duplicate values.
• RANK assigns a unique rank to each row and leaves gaps in the ranking sequence when encountering duplicate values.
• DENSE_RANK also assigns a unique rank to each row but does not leave gaps in the ranking sequence when encountering duplicate values.

When to Use Each Function:

1. Use ROW_NUMBER when you need a unique identifier for each row, such as when paginating results or creating a primary key for temporary tables.
2. Use RANK when you want to assign ranks to a dataset based on a particular column and don’t want to skip ranks in case of ties. It’s useful when you want to see the relative position of each row within the ordered set.
3. Use DENSE_RANK when you want to assign ranks to a dataset based on a particular column but don’t want any gaps in the ranking sequence, even in case of ties. DENSE_RANK is appropriate when you need to perform calculations, like finding the top N elements, without skipping any ranks.

In conclusion, ROW_NUMBER, RANK, and DENSE_RANK are powerful window functions in SQL that allow for efficient data analysis and ranking operations. Understanding their differences is crucial for utilizing them effectively in various scenarios. Whether you need a unique row identifier, a ranking with gaps, or a ranking without gaps, these functions provide valuable insights into your data and make SQL queries more versatile and informative.