SQL Window Functions: Ranking, Top N per Group, and Percent Rank

SQL Window Functions•Permalink

Ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST.

View

Standard Detailed Compact

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Ranking functions

ROW_NUMBER

↗#

SELECT employee_id,
       department_id,
       salary,
       ROW_NUMBER() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rn
FROM employees;

# Unique sequence with no ties.

RANK

↗#

SELECT employee_id,
       department_id,
       salary,
       RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

# Tied rows share rank and leave gaps.

DENSE_RANK

↗#

SELECT employee_id,
       department_id,
       salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS dense_salary_rank
FROM employees;

# Tied rows share rank without gaps.

Split rows into quartiles with NTILE

↗#

SELECT customer_id,
       lifetime_value,
       NTILE(4) OVER (ORDER BY lifetime_value DESC) AS quartile
FROM customers;

# Bucket ordered rows into four groups.

Calculate percent rank

↗#

SELECT player_id,
       score,
       PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM leaderboard;

# Relative position from 0 to 1.

Cumulative distribution

↗#

SELECT player_id,
       score,
       CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM leaderboard;

# Fraction of rows at or below the current row.

## Top N per group

Get the latest row per group

↗#

WITH ranked AS (
  SELECT o.*, 
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC
         ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

# Use ROW_NUMBER in a subquery or CTE.

Get top 3 rows per group

↗#

WITH ranked AS (
  SELECT product_id,
         category_id,
         revenue,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER BY revenue DESC
         ) AS rn
  FROM product_metrics
)
SELECT *
FROM ranked
WHERE rn <= 3;

# Keep the highest-value rows within each partition.

Return top values including ties

↗#

WITH ranked AS (
  SELECT employee_id,
         department_id,
         salary,
         RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employees
)
SELECT *
FROM ranked
WHERE rnk <= 3;

# Use RANK when tied rows should all qualify.

More in SQL Window Functions

SQL Window Functions: Performance, EXPLAIN, and Dialect Notes

How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.

SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization

Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.

SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis

Percent rank, cumulative distribution, percentile-style bucketing, and segmentation patterns.

SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics

Positional window functions for first, last, and nth values inside ordered partitions.

SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons

Compare rows to prior or next rows with LAG and LEAD for change detection, retention, and trend analysis.

SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics

Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.

Recommended next

No recommendations yet.

Browse all sheets →