ORDER BY and top-N

Here is the uncomfortable fact behind this lesson: a SQL result has no order unless you ask for one. A table is a bag of rows, not a list. The engine is free to hand them back in whatever sequence it produced them, and on a big file read by several threads at once it genuinely will differ between runs. If the order of your output matters, you have to say so.

Saying so is ORDER BY. It goes after WHERE and before LIMIT, and that position is also the order of operations: filter, then sort, then cut. This is the whole trick behind a top-N query.

Ascending, descending

ORDER BY revenue sorts smallest first, because ascending is the default. Add DESC for largest first. You can write ASC explicitly, and on a query with a mix of directions it is worth doing, because a reader should not have to remember which way the bare form goes.

Sorting works on more than numbers. Text sorts alphabetically, by character code, which means capital letters come before lower-case ones in most collations and 'Zebra' beats 'apple'. Dates sort chronologically, which is one of the reasons a real DATE column beats a string that looks like a date. And numbers stored as text sort as text: '100' lands between '10' and '11'. If a numeric sort comes back looking scrambled, that is almost always the cause, and the fix is a cast rather than a different ORDER BY.

Ties, and the second key

What happens when two rows have the same value in the sort column? Nothing defined. The engine may put either first, and it may not decide the same way twice. On a report that people compare week to week, that shows up as rows mysteriously swapping places when nothing changed.

The fix is a second sort key: ORDER BY units DESC, date ASC sorts by units, and within each group of equal units, by date. Keep adding keys until the combination is unique. In this file, date is unique across all one hundred rows, so any sort ending in date is completely determined.

This matters here in a very concrete way: three of the four exercises below check the order of your rows, not just their contents. A query that returns the right rows in a different order will be marked wrong, which is the only place in the course where the grader is that strict, and it is strict on purpose.

Nulls sort somewhere

Every database has an opinion about where NULL goes in a sorted list, and they disagree. DuckDB and Postgres put nulls last when ascending and first when descending. MySQL does the opposite. You can settle it yourself with ORDER BY score DESC NULLS LAST, and on any report where missing values exist, you should, because "the top ten" beginning with three blanks is a bug report waiting to be filed.

The top-N pattern

Sort descending, limit to N. That is the whole recipe, and it answers a surprising share of the questions anyone will ever ask you:

SELECT product, revenue
FROM csv
ORDER BY revenue DESC
LIMIT 5

Flip to ASC for the bottom five. Add a WHERE above it to ask the question within a slice, such as the best Partner sales rather than the best sales overall.

What this pattern cannot do is "the top three in each region". That needs a window function and it is lesson 13. Plenty of people spend years reaching for increasingly elaborate combinations of LIMIT to fake it. When you notice yourself doing that, you have found the moment to learn windows.

Sorting the grid is not sorting the query

The result grid on this page has clickable headers, and clicking one re-sorts what is on screen. That is a display convenience and it does not change your query. It also cannot change what the LIMIT already threw away: if your query took the first ten rows in arbitrary order, sorting those ten in the grid does not make them the top ten of the file. Sort in the query when the answer depends on it.

What to look up next

Search for "SQL collation". It is the reason two databases sort the same list of names differently, and the reason an accented character can end up at the bottom of an alphabetical report. You do not need it today, but you will recognise the symptom when it appears.