Sorting, limits, and the rows that move between runs

A query without ORDER BY has no order

This surprises people, so state it plainly: unless you write ORDER BY, the database is free to return rows in any order it likes, and it will change its mind. The same query can come back in a different order after an index is added, after rows are updated, or when the planner decides to use several CPU cores.

It usually looks stable, which is what makes it dangerous. A report that has been fine for eight months starts showing a different “top 10” because someone ran a maintenance job.

SELECT full_name FROM students LIMIT 10;            -- ten arbitrary students
SELECT full_name FROM students ORDER BY id LIMIT 10; -- the same ten, always

LIMIT without ORDER BY does not mean “the first ten”. It means “any ten, stop early”.

Ties do the same thing at a smaller scale

Now the subtler version. You sort by a column with repeated values:

SELECT id, full_name, class_level
FROM students
ORDER BY class_level DESC
LIMIT 20;

If forty students are in class 10, the twenty you get are an arbitrary twenty of the forty, and re-running may hand you a different twenty. The rule that fixes it is short: always end ORDER BY with something unique.

ORDER BY class_level DESC, id

Now the ordering is total. Nothing can move. Paginating a list without a unique tiebreaker is the cause of the classic bug where a row appears on both page 1 and page 2, and another row appears on neither.

Where NULLs sort

Ascending in Postgres, NULLs come last. Descending, they come first. Other databases disagree: MySQL puts NULLs first when ascending. So a “worst performers” report ordered ORDER BY score DESC may open with every student who has no score at all.

Say what you want:

ORDER BY score DESC NULLS LAST

SQLite and MySQL lack that clause; the portable trick is to sort on a flag first:

ORDER BY (score IS NULL), score DESC

false sorts before true, so rows with a score come first.

Sorting text is not alphabetical

ORDER BY full_name sorts by the database’s collation, and collations disagree about real questions. Whether uppercase sorts before lowercase. Whether Ångström files under A or after Z. Whether ä equals a. Whether accents are ignored. A Postgres cluster initialised with the C locale sorts by raw byte value, which puts every capital letter before every lowercase one, so Zara precedes ade.

That is not broken, it is a different definition of order. If a person will read the list, ask for a human collation explicitly:

SELECT full_name FROM students ORDER BY full_name COLLATE "en_US.utf8";

And be aware of the operational side: a collation change during an operating system upgrade can silently invalidate text indexes, because the index was built in one order and the database now believes in another. This has caused real corruption incidents. It is one of the few database problems that a completely correct query cannot protect you from.

Sorting by something you computed

You can order by an alias or by position, and one of those is a trap:

SELECT city, count(*) AS orders
FROM orders GROUP BY city
ORDER BY orders DESC;     -- fine, clear

ORDER BY 2 DESC;          -- works, but breaks the day someone adds a column

Ordinals are convenient in a throwaway query and a liability in anything saved.

DISTINCT is a reshaping, not a filter

SELECT DISTINCT city FROM customers returns each city once. Two things worth knowing.

First, DISTINCT applies to the whole selected row, not the first column. SELECT DISTINCT city, id FROM customers returns every row, because the id makes each one unique. People write this expecting one row per city and get the full table.

Second, DISTINCT is often a sign that a join is multiplying rows. The instinct to add it to make a count look right is the instinct to hide the fault rather than find it. If a SELECT DISTINCT fixes your row count, go back and ask which join matched twice: the DISTINCT will also have quietly merged different rows that happened to look alike.

The habit

Two rules that cost nothing and save whole afternoons:

• Anything with LIMIT gets an ORDER BY.
• Any ORDER BY ends with a unique column.

Then a query returns the same rows today, tomorrow and after the database is upgraded, which is the minimum standard for a number anybody is going to act on.