Ranking Functions

Need to find the top 3 products in each category? Show a leaderboard? Remove duplicate records? Ranking functions are your answer.

The Four Ranking Functions

Visual Comparison

Consider these test scores: 95, 95, 90, 85, 85, 80

Notice: ROW_NUMBER always has unique values, RANK skips numbers (1,1,3), DENSE_RANK doesn't (1,1,2).

ROW_NUMBER(): Unique Sequential Numbers

Basic Syntax

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

Deduplication: Keep Most Recent

-- Find duplicate emails, keep only the most recent user
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC
    ) as rn
  FROM users
)
SELECT * FROM ranked WHERE rn = 1;

Pagination

-- Get page 3 (rows 21-30) of products
WITH numbered AS (
  SELECT *,
    ROW_NUMBER() OVER (ORDER BY name) as row_num
  FROM products
)
SELECT * FROM numbered
WHERE row_num BETWEEN 21 AND 30;

RANK(): Gaps After Ties

SELECT
  name,
  sales,
  RANK() OVER (ORDER BY sales DESC) as sales_rank
FROM salespeople;

Notice: Bob and Carol tie at rank 2, Dave is rank 4 (not 3). Like Olympic rankings!

DENSE_RANK(): No Gaps

SELECT
  name,
  sales,
  DENSE_RANK() OVER (ORDER BY sales DESC) as sales_rank
FROM salespeople;

Now Dave is rank 3. No gaps!

NTILE(n): Divide into Buckets

NTILE divides rows into n roughly equal groups.

SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) as quartile
FROM employees;

Use Case: Percentile Analysis

-- Find customers in top 10% by spending
SELECT *
FROM (
  SELECT
    customer_id,
    total_spent,
    NTILE(10) OVER (ORDER BY total_spent DESC) as decile
  FROM customer_spending
) ranked
WHERE decile = 1;

Real-World: Top N Per Group

-- Top 3 products per category by revenue
WITH ranked_products AS (
  SELECT
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) as rank
  FROM products
)
SELECT category, product_name, revenue
FROM ranked_products
WHERE rank <= 3;

Key Takeaways

Next up: Aggregate Window Functions - Running totals, moving averages, and more.