SQL
Calculating Row Ranks and N-th Largest Values using Window Functions
Determine rankings (e.g., top N, N-th largest) or partition data using advanced SQL window functions like ROW_NUMBER(), RANK(), and DENSE_RANK().
SELECT
*,
ROW_NUMBER() OVER (ORDER BY Score DESC) AS OverallRank,
RANK() OVER (ORDER BY Score DESC) AS RankWithTies,
DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRankWithTies,
ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Score DESC) AS RankInCategory
FROM
Leaderboard
ORDER BY
OverallRank ASC;
-- Example: Get the 3rd highest score overall
WITH RankedScores AS (
SELECT
UserID,
Score,
ROW_NUMBER() OVER (ORDER BY Score DESC) AS rn
FROM
Leaderboard
)
SELECT UserID, Score
FROM RankedScores
WHERE rn = 3;
How it works: Window functions perform calculations across a set of table rows that are related to the current row. This snippet demonstrates `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` for assigning ranks based on a score, both globally and within specified categories (`PARTITION BY`). It also shows how to use a CTE with `ROW_NUMBER()` to easily retrieve the N-th largest value (e.g., the 3rd highest score), which is powerful for creating leaderboards, identifying top performers, or analyzing sequential data.