Window Functions ROW_NUMBER, RANK and DENSE_RANK

Window functions allow you to assign sequential numbers or ranking positions to the rows of a query without collapsing the original results.

Until now, when we wanted to calculate aggregates (like sums or counts), we had to use GROUP BY. But that came at a cost: we lost the detail of the individual rows. Everything was collapsed into a summary.

What if I want to know the “Top 3 sales” but without losing the information of who made the sale and when? Or if I want to number the rows of a query?

This is where window functions come into play. In this article, we will focus on Ranking functions: ROW_NUMBER, RANK and DENSE_RANK.

The OVER Clause

The OVER clause defines a window of data over which an aggregate function will be applied. This window can be the entire table, a subset of rows, or even rows related to the current row.

The basic syntax is as follows:

AGGREGATE_FUNCTION() OVER ( [PARTITION BY column] [ORDER BY column] )

The Three OVER Companions

Imagine we took a class exam and have the following grades. Let’s see how each function scores.

Notice that Ana and Pedro are tied with €800. This is where the differences arise.

Code Example

Let’s see it with a real query on a Sales table.

SELECT 
    Salesperson,
    Amount,
    ROW_NUMBER() OVER (ORDER BY Amount DESC) as Row,
    RANK()       OVER (ORDER BY Amount DESC) as Ranking,
    DENSE_RANK() OVER (ORDER BY Amount DESC) as DenseRanking
FROM Sales;

Result:

The Power of PARTITION BY

The PARTITION BY clause is optional and is used inside OVER to divide the data into smaller groups, called partitions.

This allows us to reset the ranking for each data group.

For example, we want to know who the top 2 salespeople are in each department.

SELECT * FROM (
    SELECT 
        Department,
        Salesperson,
        Sales,
        DENSE_RANK() OVER (
            PARTITION BY Department -- Resets the counter when Department changes
            ORDER BY Sales DESC
        ) as Position
    FROM Employees
) DerivedTable
WHERE Position <= 2;

WITH CTE_Duplicates AS (
    SELECT 
        Name, 
        Email,
        -- Number the repetitions. The first will be 1, the duplicate will be 2, etc.
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY RegistrationDate) as Nro
    FROM Users
)
DELETE FROM CTE_Duplicates
WHERE Nro > 1;