ROW_NUMBER, RANK and DENSE_RANK: choose a tie policy

Equal scores reveal different requirements

For scores 100, 100 and 90, ROW_NUMBER can assign 1, 2 and 3, RANK assigns 1, 1 and 3, and DENSE_RANK assigns 1, 1 and 2. These answer different questions. Do you need exactly one winner, shared places with gaps, or a sequence of distinct score groups? Make that requirement explicit before selecting the function.

Use a stable tie-breaker only when you want to break ties

For exactly one winner, order ROW_NUMBER by score descending and a stable identifier. For shared winners, rank by score alone within each group. Adding the unique identifier to DENSE_RANK’s ordering makes every row distinct and removes the tie that you wanted to preserve.

WITH ranked AS (
  SELECT id, customer, amount,
    DENSE_RANK() OVER (
      PARTITION BY customer ORDER BY amount DESC
    ) AS position
  FROM sales WHERE amount IS NOT NULL
)
SELECT id, customer, amount FROM ranked
WHERE position = 1 ORDER BY customer, id;

Keep ranking order and display order separate

The window’s ORDER BY defines rank. The final ORDER BY defines the displayed sequence. Filtering out NULL amounts before ranking states that an unknown amount cannot win. A customer with only NULL amounts therefore has no returned winner under this contract.

Test ties, absence and scale

Test equal winners, one customer with only NULL amounts, an empty table and several independent customers. Sorting often dominates the work, but an appropriate index or engine strategy can change the plan. Inspect the plan on your actual database and data distribution. Do not assume SQLite’s plan describes MySQL or PostgreSQL behavior.