Sorting, limits, and the illusion of order

Why a query without ORDER BY has no order at all, even when it looks like it does.

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 10;

That reads as "the top ten". It is — but only because of ORDER BY.

Without ORDER BY there is no order

A table is a set of rows. The database is free to return them in whatever order is cheapest, and that order can change when the data grows, when an index is added, or when the plan changes. It will often look stable in development and then differ in production.

If order matters, say so. Never rely on the order rows happen to come back in.

LIMIT without ORDER BY is worse

LIMIT 10 with no ORDER BY means "any ten rows". It will look like the first ten. It is not promised to be, and "top ten" reports built this way have shipped wrong numbers for months without anyone noticing.

Ties

If two students have the same marks, ORDER BY marks DESC does not say which comes first. Add a tiebreaker that is unique:

ORDER BY marks DESC, id ASC;

Now the result is reproducible, which is what makes it testable.