Unit 02.03: Sorting, ties, and non-deterministic LIMIT

Unit ID: SQL-M02-U04 - Estimated active time: 13-16 minutes Objective: recognise when ORDER BY … LIMIT can return different rows between runs, and make it stable.

"Top 3" is not always the same 3

SELECT order_id, order_total
FROM orders
ORDER BY order_total DESC
LIMIT 3;
-- 608 | 4996.00
-- 243 | 4991.00
-- 851 | 4987.00

Stable here, because those three totals are distinct. Now look at a real tie in our data:

SELECT order_id, order_total
FROM orders
WHERE order_total = 1000.00;
-- 500 | 1000.00
-- 501 | 1000.00

Two orders share exactly ₹1,000.00. If a query sorts by order_total and cuts the result anywhere across that boundary, which of the two survives is not defined by SQL. The engine may return either. It may return a different one after an index change, a version upgrade, or a parallel plan.

Making it deterministic

Add a tie-breaker that is unique:

SELECT order_id, order_total
FROM orders
ORDER BY order_total DESC, order_id ASC
LIMIT 3;

order_id is the primary key, so no two rows can tie on the full sort. The result is now reproducible.

Why this matters more than it looks

A "top 10 customers" list that quietly changes between runs destroys trust in a report even when every individual number is correct. The reader cannot tell an unstable sort from a real change in the data.

-- Unstable: many orders share a status
SELECT order_id, status FROM orders ORDER BY status LIMIT 5;

-- Stable
SELECT order_id, status FROM orders ORDER BY status, order_id LIMIT 5;

Practice

Write a query for the 5 highest-value completed orders that returns the same rows every single time it runs, and say which column guarantees that.

SELECT order_id, order_total
FROM orders
WHERE status = 'completed'
ORDER BY order_total DESC, order_id ASC
LIMIT 5;

Takeaway

Any ORDER BY … LIMIT without a unique final sort column is a coin flip at the boundary. Add the key.

---

Mark complete