Skip to main content

RANK

Returns the rank of the current row with gaps. This is the ROW_NUMBER of the first peer in each group. Rows with equal values for the ordering columns receive the same rank, and the next rank value is incremented by the number of tied rows (leaving gaps).

If there is no ORDER BY clause, then all rows are considered peers and this function always returns 1.

See also: [SELECT] [ROW_NUMBER] [DENSE_RANK]

Syntax​

SELECT RANK() OVER ([PARTITION BY column] ORDER BY column [ASC|DESC])
FROM <multipartIdentifier>;

Arguments​

PARTITION BY column Optional. Divides the result set into partitions. The RANK function is applied to each partition separately.

ORDER BY column Specifies the order in which ranks are assigned. If there is no ORDER BY clause, all rows are considered peers and receive rank 1.

Return Value(s)​

Returns an integer representing the rank of the current row within the partition, with gaps for tied values.

Examples​

Rank contributors by contributions (with gaps)​

-- Rank contributors with gaps for ties
SELECT
    login,
    contributions,
    RANK() OVER (ORDER BY contributions DESC) as rank
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

Compare RANK with DENSE_RANK and ROW_NUMBER​

-- Compare RANK vs DENSE_RANK vs ROW_NUMBER for contributors
SELECT
    login,
    contributions,
    RANK() OVER (ORDER BY contributions DESC) as rank,
    DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

Rank contributors within each repository using PARTITION BY​

-- Rank contributors within each repo
WITH repo_contributors AS (
    SELECT
        repo,
        login,
        contributions,
        RANK() OVER (PARTITION BY repo ORDER BY contributions DESC) as rank_in_repo
    FROM (
        SELECT 'stackql' as repo, login, contributions
        FROM github.repos.contributors
        WHERE owner = 'stackql' AND repo = 'stackql'
        UNION ALL
        SELECT 'stackql-provider-registry' as repo, login, contributions
        FROM github.repos.contributors
        WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
    ) t
)
SELECT
    repo,
    login,
    contributions,
    rank_in_repo
FROM repo_contributors
ORDER BY repo, rank_in_repo;

For more information, see https://sqlite.org/windowfunctions.html#built-in_window_functions.