Top N per Group With Ties

SQLRanking with tiesSeniorStaffUpdated Nov 10, 2026

Return the top 2 sales reps in each region by total amount. If two reps tie, both should appear and the next rep should be skipped (classic 1, 2, 2, 4 ranking).

Schema

CREATE TABLE sales (
  id        BIGINT PRIMARY KEY,
  region    TEXT NOT NULL,
  rep_name  TEXT NOT NULL,
  amount    NUMERIC NOT NULL,
  sold_at   DATE NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT region, rep_name, total
FROM (
  SELECT region, rep_name, SUM(amount) AS total,
         RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY region, rep_name
) t
WHERE rk <= 2;

RANK() produces 1, 2, 2, 4: exactly the 'include ties, skip the next slot' semantics asked for. ROW_NUMBER (a) breaks ties arbitrarily and would drop a tied rep. DENSE_RANK (c) would let a third rep through because it produces 1, 2, 2, 3. LIMIT 2 (d) returns 2 rows globally, not 2 per region.

Going Deeper

ROW_NUMBER, RANK, and DENSE_RANK only differ on ties. Pick by what the product says about ties: arbitrary tiebreak (ROW_NUMBER), include-and-skip (RANK), include-and-continue (DENSE_RANK).

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT region, rep_name, total
FROM (
  SELECT region, rep_name, SUM(amount) AS total,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn
  FROM sales
  GROUP BY region, rep_name
) t
WHERE rn <= 2;

Option (c)

SELECT region, rep_name, total
FROM (
  SELECT region, rep_name, SUM(amount) AS total,
         DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS dr
  FROM sales
  GROUP BY region, rep_name
) t
WHERE dr <= 2;

Option (d)

SELECT region, rep_name, SUM(amount) AS total
FROM sales
GROUP BY region, rep_name
ORDER BY region, total DESC
LIMIT 2;