ROW_NUMBER() | RANK() | DENSE_RANK()

You working with window functions , must’ve stumbled on these. At first, I thought row_number() and rank() were similar. Hence, I did some digging and found many have the same doubt. Always delight to know, I am not alone. So, let’s figure out what these 3 window functions do.

ROW_NUMBER()

Definition: ROW_NUMBER() is a window function in SQL that returns a unique sequential integer for each row within a defined window partition of a result set. The numbering starts at 1 for the first row in each partition and continues sequentially based on the ordering specified in the OVER clause.

RANK()

Definition: RANK() is a window function that assigns a rank to each row within a partition of a result set. Rows with the same ordering values receive the same rank, and the next rank value is skipped accordingly.

DENSE_RANK()

Definition: DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set. Rows with equal values receive the same rank, and no rank values are skipped when ties occur.

Get Sivani-tutika’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

In short, row_number starts numbering 1,2,3,4 …. rank() based on the condition in OVER() starts ranking 1,2,3,4… if tie it will rank 5,5 skip 6 and starts from 7. dense_rank() also ranks 1,2,3,4 if tie it will rank 5,5 but next will be 6 instead of 7.

Here is an example.

SELECT
    employee,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC)  AS row_number,
    RANK() OVER (ORDER BY salary DESC)        AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

| employee | salary | row_number | rank | dense_rank |
| -------- | ------ | ---------- | ---- | ---------- |
| Alice    | 10000  | 1          | 1    | 1          |
| Bob      | 9000   | 2          | 2    | 2          |
| Carol    | 9000   | 3          | 2    | 2          |
| Dave     | 9000   | 4          | 2    | 2          |
| Luffy    | 5000   | 5          | 5    | 3          |
| Nami     | 4000   | 6          | 6    | 4          |
| Robin    | 4000   | 7          | 6    | 4          |