Skip to content

SQL - PostgreSQL - Ranking Data

Source: sql-snippets/rank.md at main · count/sql-snippets (github.com)

Description

A common requirement is the need to rank a particular row in a group by some quantity - for example, ‘rank stores by total sales in each region’.

Fortunately, PostgreSQL natively has a rank window function to do this.

The syntax looks like this:

SELECT
  RANK() OVER(PARTITION BY <fields> ORDER BY <rank_by>) AS RANK
FROM <schema.tablename>

where

  • fields - zero or more columns to split the results by - a rank will be calculated for each unique combination of values in these columns
  • rank_by - a column to decide the ranking
  • schema.table - where to pull these columns from

Example

We want to find out, for our random data sample of fruits, which fruit is most popular.

WITH data AS (
    SELECT CASE
               WHEN RANDOM() <= 0.25 THEN 'apple'
               WHEN RANDOM() <= 0.5 THEN 'banana'
               WHEN RANDOM() <= 0.75 THEN 'pear'
               ELSE 'orange'
               END AS fruit,
           RANDOM() + (series^RANDOM()) AS popularity
    FROM generate_series(1, 10) AS series
    )

SELECT
  fruit,
  popularity,
  RANK() OVER(ORDER BY popularity DESC) AS popularity_rank
FROM data

Output:

fruit popularity popularity_rank
banana 3.994866211350613 1
banana 3.2802621307398208 2
banana 2.626913466142052 3
pear 2.59071184223806 4
banana 2.137745293125355 5
apple 2.0741654350518637 6
apple 2.0401770856253933 7
pear 1.771223675755631 8
banana 1.743055659082957 9
pear 1.7158950640518675 10

Backlinks:

list from [[SQL - PostgreSQL - Ranking Data]] AND -"Changelog"