Comparing ROW_NUMBER(), RANK(), and DENSE_RANK() Functions in SQL

ROW_NUMBER(), RANK(), and DENSE_RANK() are all functions in SQL that are used to assign unique rankings to rows in a result set. They are similar, but have some important differences:

Photo by Caspar Camille Rubin on Unsplash

Also Hey ! Show your support and appreciation by clapping for my posts!

SQL Basics Simplified: 2023

1. ROW_NUMBER(): This function assigns a unique number to each row in the result set, based on the order specified in the ORDER BY clause. The numbers are sequential and have no gaps. For example:

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

This query assigns a unique row number to each employee based on their salary, in descending order.

2. RANK(): This function also assigns a unique rank to each row in the result set but takes into account ties in the values. If there are two or more rows with the same value, they are assigned the same rank, and the next rank is skipped. For example:

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

This query assigns a rank to each employee based on their salary, in descending order. If there are two employees with the same salary, they are assigned the same rank, and the next rank is skipped.

3. DENSE_RANK(): This function works similarly to RANK(), but does not skip ranks for ties. Instead, it assigns the same rank to tied values and the next rank to the next value. For example:

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

This query assigns a dense rank to each employee based on their salary, in descending order. If there are two employees with the same salary, they are assigned the same rank, and the next rank is assigned to the next value.

Here's an example of what the output might look like:

name       | salary | row_number | rank | dense_rank
-----------|--------|------------|------|-----------
John Doe   | 50000  | 1          | 1    | 1
Jane Doe   | 45000  | 2          | 2    | 2
Jim Smith  | 45000  | 3          | 2    | 2
Sarah Johnson | 40000  | 4          | 4    | 3

In this example, the employees table has four rows, and the data is ordered by salary in descending order.

• For the ROW_NUMBER() function, a unique number is assigned to each row, with no gaps.
• For the RANK() function, two employees (Jane Doe and Jim Smith) have the same salary, so they are assigned the same rank (2), and the next rank (3) is skipped.
• For the DENSE_RANK() function, two employees (Jane Doe and Jim Smith) have the same salary, so they are assigned the same rank (2), but the next rank (3) is assigned to the next value (Sarah Johnson).

So, to summarize:

• ROW_NUMBER() assigns a unique number to each row, with no gaps
• RANK() assigns a unique rank to each row, skipping ranks for tied values
• DENSE_RANK() assigns a unique rank to each row, without skipping ranks for tied values.

I hope that you will find this article insightful and informative. If you enjoyed it, please consider sharing the link. If you have any suggestions or feedback, please feel free to leave a comment/clap. And if you’d like to stay updated on my future content, please consider following and subscribing using the provided link. Thank you for your support!