Solving tips
- Combine ORDER BY with LIMIT to return a top-N slice of the rows.
- Add a deterministic tie-breaker column to ORDER BY so ties resolve predictably.
- PostgreSQL uses LIMIT; SQL Server uses TOP and Oracle uses FETCH FIRST for the same idea.
You are given an orders table and need the highest-value orders for a dashboard’s leaderboard.
Schema
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
amount NUMERIC(10,2)
);
Sample data:
| id | customer_id | order_date | amount |
|---|
| 101 | 1 | 2023-01-05 | 250.00 |
| 102 | 2 | 2023-01-06 | 500.00 |
| 103 | 1 | 2023-01-10 | 500.00 |
| 104 | 3 | 2023-02-01 | 120.00 |
| 105 | 2 | 2023-02-15 | 800.00 |
| 106 | 4 | 2023-03-01 | 300.00 |
| 107 | 3 | 2023-03-05 | 300.00 |
Task
Return the id and amount of the three orders with the largest amount. Order by amount in descending order, and when two orders have the same amount, break the tie by id in ascending order.
Expected output
| id | amount |
|---|
| 105 | 800.00 |
| 102 | 500.00 |
| 103 | 500.00 |
Approach
A top-N query is ORDER BY plus LIMIT. Sort by amount DESC to bring the biggest orders to the top, add id ASC as a secondary sort key so equal amounts have a defined order, then cap the result at three rows with LIMIT 3.
Query
SELECT id, amount
FROM orders
ORDER BY amount DESC, id ASC
LIMIT 3;
Walkthrough
Sorting by amount descending gives 800 (id 105), then the two 500 orders (ids 102 and 103), then 300, 300, 250, 120. The tie between the two 500 orders is resolved by id ASC, so 102 precedes 103. LIMIT 3 keeps the first three rows: 105 at 800.00, 102 at 500.00, and 103 at 500.00. Order 106 at 300 is just below the cut.
Complexity & notes
With an index on amount the planner can perform a top-N heap sort that avoids fully sorting the table. The tie-breaker matters: without id ASC, the relative order of the two 500 orders would be arbitrary and could change between runs. Dialect differences on the limit clause: PostgreSQL and MySQL use LIMIT 3, SQL Server uses SELECT TOP 3, and Oracle uses FETCH FIRST 3 ROWS ONLY.