Level 5 · The ten biggest refunds we caused

A top-N list is three clauses working together: filter, sort, cut. You used all three on the price list; here they run on a new table, returns.

Filter, sort, cut

SELECT return_id, reason, refund_amount
FROM returns
WHERE reason IN ('damaged', 'wrong_item')
ORDER BY refund_amount DESC, return_id ASC
LIMIT 3;

The top three are return 802 (damaged, $4,827.00), return 1200 (wrong item, $3,996.00) and return 641 (damaged, $3,318.00). The database applies the steps in this order: WHERE removes the other reasons, ORDER BY sorts what is left, and LIMIT keeps the first rows of the sorted result. LIMIT without ORDER BY returns “some” rows, not the top ones.

ASC is the default, so return_id ASC and plain return_id are the same. Writing it out makes mixed directions easy to read: largest refund first, but lowest id first among ties.

Why the tie-breaker matters here

Returns 31 and 317 both refunded exactly $3,198.00. Both fit inside the top ten, but which one is listed first is up to the database unless you say so. If a tie fell right at the cut-off, between rows 10 and 11, the database could even pick a different row each time. A unique column as the last sort key, here return_id, removes the doubt.

Common mistakes

• Sorting ascending by accident, which gives the ten smallest refunds instead of the largest.
• Putting LIMIT before ORDER BY. SQL expects ORDER BY first, then LIMIT, and reports a syntax error otherwise.
• Filtering with reason = 'damaged' OR reason = 'wrong_item' and then adding more AND conditions without parentheses. IN keeps it safe.
• Reading the result without the sort column. Keep refund_amount in the SELECT list, so anyone can see at a glance that the list really runs from largest to smallest.

In SQL Server

SQL Server has no LIMIT; it puts TOP n right after SELECT. TOP 10 WITH TIES would also keep any extra rows that tie with the tenth one:

SELECT TOP 10 return_id, refund_amount
FROM returns
WHERE reason IN ('damaged', 'wrong_item')
ORDER BY refund_amount DESC, return_id ASC;