×

sql · level 3 · unit 2 · lesson 3

  1. meet it
  2. guess first
  3. your turn
  4. fix it
  5. nailed it

Tap a green word if you get lost.

meet it

and let tied rows share a place.

In a bake-off, two bakers with the same score deserve the same place. ROW_NUMBER cannot do that, because every row gets its own number. RANK and DENSE_RANK give tied rows the same place.

They differ after a tie. RANK skips, like a sports table: two bakers share 2nd, so the next is 4th. DENSE_RANK never skips, so the next is 3rd. That suits questions like the three best scores.

Here Ava and Sam both scored 24 in total, so they share second place.

WITH totals AS (
  SELECT baker, SUM(score) AS points
  FROM entries GROUP BY baker
)
SELECT baker, points,
  RANK() OVER (ORDER BY points DESC) AS rank_place,
  DENSE_RANK() OVER (ORDER BY points DESC) AS dense_place
FROM totals
ORDER BY points DESC, baker;

Tied rows share a place, so a tie-breaker here only decides who is listed first.