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

“With gaps” means that it returns the same rank for any ties (i.e. two or more rows with the same value), but then subsequent ranks jump forward to account for the ties.

This means that there’s the potential for noncontiguous rank values. For example it could go 1, 2, 5, etc if several rows are ranked at 2. If there are no ties, then the rank values will be contiguous.

Syntax

The syntax goes like this:

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,
    rank() OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice | 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 |    8
     1003 | Straw Dog Box                   |        55.99 |    9
     1003 | Chainsaw                        |       245.00 |   10
(10 rows)

Here, the left handed screwdriver and right handed screwdriver share the same rank. That’s because they are both the same price. We can see that there’s a gap between that rank (6) and the next rank (8). In other words, there’s no rank of 7. This is how the rank() function works.

If you don’t want such gaps, use dense_rank() instead. That function does the same thing as rank() but with no gaps.

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,
    rank() OVER ( )
FROM products;

Result:

 vendorid |           productname           | productprice | 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 rank() 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,
    rank()
FROM products;

Result:

ERROR:  window function 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,
    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 | 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,
    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 | rank 
---------------+---------------------------------+--------------+------
 Mars Supplies | Left handed screwdriver         |        25.99 |    1
 Mars Supplies | Right handed screwdriver        |        25.99 |    1
 Mars Supplies | Long Weight (blue)              |        14.75 |    3
 Mars Supplies | Long Weight (green)             |        11.99 |    4
 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. We can see that the next rank value is 3, due to the tie at number 1.

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

SELECT 
    v.vendorname,
    p.productname,
    p.productprice,
    rank() OVER ( 
        PARTITION BY p.vendorid 
        ORDER BY p.productprice ASC
        ) AS "ASC",
    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 |    4
 Mars Supplies | Long Weight (blue)              |        14.75 |   2 |    3
 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,
    rank() OVER ( 
        win 
        ORDER BY p.productprice ASC
        ) AS "ASC",
    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 |    4
 Mars Supplies | Long Weight (blue)              |        14.75 |   2 |    3
 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.