// window functions

ROW_NUMBER, RANK, DENSE_RANK: three ways to number rows within a group

All three give each row a number within a group. They only disagree on ties, and that disagreement is the whole interview question.

Published 12 Jul 20268 min read35 reads

So far every SQL trick you've learned works on rows independent of their neighbors. A window function is different: it lets a row "look around" at other rows in its group without collapsing them the way GROUP BY does. ROW_NUMBER, RANK, and DENSE_RANK are the simplest window functions: they number rows within a group.

🎯 Explain Like I'm Hired Imagine ranking runners in a race, split into age groups. ROW_NUMBER gives every runner a unique place, even if two tie: one gets 2nd and the other gets 3rd, arbitrarily. RANK gives tied runners the same place, then skips a number (two 2nd-places means the next runner is 4th). DENSE_RANK also ties them, but doesn't skip (the next runner is 3rd). Example: "get each customer's most recent order" is ROW_NUMBER + keep only rn = 1. You want exactly one row per customer, and it doesn't matter how ties are broken.

SELECT
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;

PARTITION BY customer_id says "restart the numbering for each customer" (this is the window-function version of GROUP BY (it makes separate groups), except rows aren't collapsed). ORDER BY order_date DESC says "number them newest first." So rn = 1 is each customer's most recent order.

Sign up to keep reading

Sign up free to unlock the worked examples, edge cases, and interview traps below.

#sql#window-functions#ranking#row-number#rank#dense-rank
← Back to articles