ROW_NUMBER and RANK

Why it matters

"Top 3 products in each category." "Each customer's most recent order." "Remove duplicate rows but keep the newest one." These are some of the most common requests an analyst gets, and GROUP BY cannot answer them, because grouping collapses rows and you lose the detail. Window functions compute a value across a set of related rows while keeping every row. Ranking functions are the place to start.

The core idea: OVER (...)

A window function is any function followed by OVER (...). The brackets describe the "window" of rows it looks at:

• PARTITION BY col splits rows into groups (like GROUP BY, but rows are not collapsed).
• ORDER BY col sets the order inside each partition.

SELECT category, name, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
FROM products;

The numbering restarts at 1 for each category. Every product row is still there.

ROW_NUMBER, RANK and DENSE_RANK

The three differ only in how they treat ties (Trail Shoe and Hiking Boot both have 18,400):

• ROW_NUMBER() always gives distinct numbers. Which tied row gets 2 and which gets 3 is arbitrary unless you add a tie-breaker, such as ORDER BY revenue DESC, name.
• RANK() gives ties the same rank and then skips (1, 2, 2, 4), like a race where two runners share silver and nobody gets bronze.
• DENSE_RANK() gives ties the same rank without gaps (1, 2, 2, 3).

Choose by the business rule: "exactly one row per group" means ROW_NUMBER; "top 2, and include ties" means RANK or DENSE_RANK.

Filtering on a rank: use a CTE

You cannot write WHERE rn <= 2 in the same query, because WHERE runs before window functions are computed. Compute the rank in a CTE, then filter:

WITH ranked AS (
  SELECT category, name, revenue,
         RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
  FROM products
)
SELECT category, name, revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 2;

This "rank in a CTE, filter outside" shape is the standard top-N-per-group pattern. (Some warehouses such as Snowflake, BigQuery and Databricks offer a QUALIFY clause that filters window results directly; SQLite and PostgreSQL do not.)

Two more everyday uses

Latest row per entity. Each customer's most recent order:

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

De-duplication. If a load inserted the same record twice, keep one copy by ranking within the duplicate key and keeping rn = 1. The id DESC tie-breaker makes the choice repeatable.

Without PARTITION BY

Leave out PARTITION BY and the whole result is one window, so you get an overall ranking:

SELECT name, revenue,
       DENSE_RANK() OVER (ORDER BY revenue DESC) AS overall_rank
FROM products;

Also useful: NTILE(4) OVER (ORDER BY revenue DESC) splits rows into four roughly equal buckets (quartiles), and PERCENT_RANK() gives a 0 to 1 position.

Window functions vs GROUP BY

You can combine them: a window function can run over an already grouped result, such as ranking regions by SUM(amount). Window functions run after GROUP BY and HAVING, and before the final ORDER BY and LIMIT.

Common mistakes

• Filtering a window function in WHERE of the same query: an error. Wrap it in a CTE.
• No tie-breaker with ROW_NUMBER, so the "top" row changes between runs.
• ORDER BY revenue instead of ORDER BY revenue DESC, which ranks the smallest first.
• Forgetting PARTITION BY when you wanted a per-group rank.
• Using LIMIT 2 for "top 2 per category": LIMIT applies to the whole result, not per group.

Quick recap

• function() OVER (PARTITION BY ... ORDER BY ...) computes across related rows without collapsing them.
• ROW_NUMBER = unique numbers; RANK = ties share, then gaps; DENSE_RANK = ties share, no gaps.
• Top N per group: rank in a CTE, filter outside.

Try it

Open the Do tab and return the top 2 revenue ranks per category, keeping ties.