Understanding the DENSE_RANK() Function in PostgreSQL

Posted on by Ian

PostgreSQL has a window function called dense_rank() that returns the rank of the current row, without gaps.

It works the same way that the rank() function works, but without gaps (the rank() function includes gaps).

“Without gaps” means that, although it returns the same rank for any ties (i.e. two or more rows with the same value), there are no gaps between that rank and the next rank.

As a consequence, all rank values are contiguous. For example, if two rows tie for second place, they will both be ranked 2, and then the next row/s will be ranked at 3, and so on. By contrast, if we used rank(), the next row would be ranked at 4 instead of 3.

Syntax

The syntax goes like this:

dense_rank() over_clause

The OVER clause is required. It determines how the rowset is partitioned and ordered before the window function is applied.

Example

Here’s a basic example to demonstrate how it works:

SELECT 
    vendorid,
    productname,
    productprice,
    dense_rank() OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice | dense_rank 
----------+---------------------------------+--------------+------------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |          1
     1003 | Hammock                         |        10.00 |          2
     1001 | Long Weight (green)             |        11.99 |          3
     1004 | Tea Pot                         |        12.45 |          4
     1001 | Long Weight (blue)              |        14.75 |          5
     1001 | Left handed screwdriver         |        25.99 |          6
     1001 | Right handed screwdriver        |        25.99 |          6
     1002 | Sledge Hammer                   |        33.49 |          7
     1003 | Straw Dog Box                   |        55.99 |          8
     1003 | Chainsaw                        |       245.00 |          9
(10 rows)

Here, the left handed screwdriver and right handed screwdriver share the same rank (because they’re both the same price). We can see that there’s no gap between that rank (6) and the next rank (7). In other words, the values in the dense_rank column are contiguous.

If we’d used rank() instead of dense_rank(), there would be no rank of 7. It would have gone straight to 8.

Let’s add a rank() column to verify this:

SELECT 
    vendorid,
    productname,
    productprice,
    dense_rank() OVER ( ORDER BY productprice ),
    rank() OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice | dense_rank | rank 
----------+---------------------------------+--------------+------------+------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |          1 |    1
     1003 | Hammock                         |        10.00 |          2 |    2
     1001 | Long Weight (green)             |        11.99 |          3 |    3
     1004 | Tea Pot                         |        12.45 |          4 |    4
     1001 | Long Weight (blue)              |        14.75 |          5 |    5
     1001 | Left handed screwdriver         |        25.99 |          6 |    6
     1001 | Right handed screwdriver        |        25.99 |          6 |    6
     1002 | Sledge Hammer                   |        33.49 |          7 |    8
     1003 | Straw Dog Box                   |        55.99 |          8 |    9
     1003 | Chainsaw                        |       245.00 |          9 |   10
(10 rows)

As predicted, the rank() function jumped straight to 8 after the tie.

Omitting the ORDER BY Clause

The ORDER BY clause plays an important role with these ranking functions. If we omit it, then all rows are considered peers and we therefore get the same rank:

SELECT 
    vendorid,
    productname,
    productprice,
    dense_rank() OVER ( )
FROM products;

Result:

 vendorid |           productname           | productprice | dense_rank 
----------+---------------------------------+--------------+------------
     1001 | Left handed screwdriver         |        25.99 |          1
     1001 | Right handed screwdriver        |        25.99 |          1
     1001 | Long Weight (blue)              |        14.75 |          1
     1001 | Long Weight (green)             |        11.99 |          1
     1002 | Sledge Hammer                   |        33.49 |          1
     1003 | Chainsaw                        |       245.00 |          1
     1003 | Straw Dog Box                   |        55.99 |          1
     1003 | Hammock                         |        10.00 |          1
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |          1
     1004 | Tea Pot                         |        12.45 |          1
(10 rows)

The OVER Clause is Required

We must provide an OVER clause when using the dense_rank() function (and any other window function). This determines how the rowset is partitioned and ordered before the window function is applied.

Omitting the OVER clause results in an error:

SELECT 
    vendorid,
    productname,
    productprice,
    dense_rank()
FROM products;

Result:

ERROR:  window function dense_rank requires an OVER clause

Partitioning

This example uses a PARTITION BY clause to partition the results by vendor:

SELECT 
    v.vendorname,
    p.productname,
    p.productprice,
    dense_rank() OVER ( 
        PARTITION BY p.vendorid 
        ORDER BY p.productprice 
        )
FROM products p INNER JOIN 
vendors v ON p.vendorid = v.vendorid;

Result:

  vendorname   |           productname           | productprice | dense_rank 
---------------+---------------------------------+--------------+------------
 Mars Supplies | Long Weight (green)             |        11.99 |          1
 Mars Supplies | Long Weight (blue)              |        14.75 |          2
 Mars Supplies | Right handed screwdriver        |        25.99 |          3
 Mars Supplies | Left handed screwdriver         |        25.99 |          3
 Randy Roofers | Sledge Hammer                   |        33.49 |          1
 Pedal Medals  | Hammock                         |        10.00 |          1
 Pedal Medals  | Straw Dog Box                   |        55.99 |          2
 Pedal Medals  | Chainsaw                        |       245.00 |          3
 Katty Kittens | Bottomless Coffee Mugs (4 Pack) |         9.99 |          1
 Katty Kittens | Tea Pot                         |        12.45 |          2
(10 rows)

We can see that the rankings now reset with each partition.

In this example I performed a SQL join on the vendors table in order to return the vendor name instead of the vendor ID.

Changing the Order

Here’s what happens when we change the order to descending order:

SELECT 
    v.vendorname,
    p.productname,
    p.productprice,
    dense_rank() OVER ( 
        PARTITION BY p.vendorid 
        ORDER BY p.productprice DESC
        )
FROM products p INNER JOIN 
vendors v ON p.vendorid = v.vendorid;

Result:

  vendorname   |           productname           | productprice | dense_rank 
---------------+---------------------------------+--------------+------------
 Mars Supplies | Left handed screwdriver         |        25.99 |          1
 Mars Supplies | Right handed screwdriver        |        25.99 |          1
 Mars Supplies | Long Weight (blue)              |        14.75 |          2
 Mars Supplies | Long Weight (green)             |        11.99 |          3
 Randy Roofers | Sledge Hammer                   |        33.49 |          1
 Pedal Medals  | Chainsaw                        |       245.00 |          1
 Pedal Medals  | Straw Dog Box                   |        55.99 |          2
 Pedal Medals  | Hammock                         |        10.00 |          3
 Katty Kittens | Tea Pot                         |        12.45 |          1
 Katty Kittens | Bottomless Coffee Mugs (4 Pack) |         9.99 |          2
(10 rows)

This time the tied rows are both ranked 1st in their partition with a rank of 1.

Here it is again, but with dense_rank() applied to two different columns, each with different ordering:

SELECT 
    v.vendorname,
    p.productname,
    p.productprice,
    dense_rank() OVER ( 
        PARTITION BY p.vendorid 
        ORDER BY p.productprice ASC
        ) AS "ASC",
    dense_rank() OVER ( 
        PARTITION BY p.vendorid 
        ORDER BY p.productprice DESC
        ) AS "DESC"
FROM products p INNER JOIN 
vendors v ON p.vendorid = v.vendorid;

Result:

  vendorname   |           productname           | productprice | ASC | DESC 
---------------+---------------------------------+--------------+-----+------
 Mars Supplies | Long Weight (green)             |        11.99 |   1 |    3
 Mars Supplies | Long Weight (blue)              |        14.75 |   2 |    2
 Mars Supplies | Right handed screwdriver        |        25.99 |   3 |    1
 Mars Supplies | Left handed screwdriver         |        25.99 |   3 |    1
 Randy Roofers | Sledge Hammer                   |        33.49 |   1 |    1
 Pedal Medals  | Hammock                         |        10.00 |   1 |    3
 Pedal Medals  | Straw Dog Box                   |        55.99 |   2 |    2
 Pedal Medals  | Chainsaw                        |       245.00 |   3 |    1
 Katty Kittens | Bottomless Coffee Mugs (4 Pack) |         9.99 |   1 |    2
 Katty Kittens | Tea Pot                         |        12.45 |   2 |    1
(10 rows)

Here, the ASC column is numbered in ascending order, while DESC is in descending order.

Named Windows

We can move the window’s definition, or part of it, to a named window.

Example:

SELECT 
    v.vendorname,
    p.productname,
    p.productprice,
    dense_rank() OVER ( 
        win 
        ORDER BY p.productprice ASC
        ) AS "ASC",
    dense_rank() OVER ( 
        win
        ORDER BY p.productprice DESC
        ) AS "DESC"
FROM products p INNER JOIN 
vendors v ON p.vendorid = v.vendorid
WINDOW win AS ( PARTITION BY p.vendorid );

Result:

  vendorname   |           productname           | productprice | ASC | DESC 
---------------+---------------------------------+--------------+-----+------
 Mars Supplies | Long Weight (green)             |        11.99 |   1 |    3
 Mars Supplies | Long Weight (blue)              |        14.75 |   2 |    2
 Mars Supplies | Right handed screwdriver        |        25.99 |   3 |    1
 Mars Supplies | Left handed screwdriver         |        25.99 |   3 |    1
 Randy Roofers | Sledge Hammer                   |        33.49 |   1 |    1
 Pedal Medals  | Hammock                         |        10.00 |   1 |    3
 Pedal Medals  | Straw Dog Box                   |        55.99 |   2 |    2
 Pedal Medals  | Chainsaw                        |       245.00 |   3 |    1
 Katty Kittens | Bottomless Coffee Mugs (4 Pack) |         9.99 |   1 |    2
 Katty Kittens | Tea Pot                         |        12.45 |   2 |    1
(10 rows)

Doing this saves us from having to replicate code across our SELECT list, but it still allows us to add clauses when referencing that named window. In this case, we referenced the named window, but we also added the ORDER BY clause directly to each OVER clause.