How Does ORDER BY Sort Results?

ORDER BY sorts the rows that survive the earlier stages of the query. You can sort by an expression, by an alias, by a column you did not project, or by an ordinal position. When several rows tie on the sort key, the order between them is unspecified — the engine is free to return them in any order it likes, and the order can differ between runs, between engines and between two versions of the same engine.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(60) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (id, name, category, unit_price) VALUES
  (1, 'Mechanical Keyboard', 'peripherals', 79.50),
  (2, 'USB-C Hub',        'peripherals', 32.00),
  (3, '27-inch Monitor',  'displays',    249.99),
  (4, 'Standing Desk',    'furniture',   419.00),
  (5, 'Webcam 4K',        'peripherals', 129.00);

SELECT name, category, unit_price
FROM products
ORDER BY category, unit_price DESC;

Five rows come back, sorted by category alphabetically, then by price descending inside each category: the 27-inch Monitor in displays, the Standing Desk in furniture, and then the three peripherals ordered as Mechanical Keyboard at 79.50, Webcam 4K at 129.00 and USB-C Hub at 32.00. The secondary key is what makes that last group deterministic — without it, those three rows would come back in an unspecified order.

That is the reason to always add a tiebreaker to an ORDER BY. Any query that feeds a paginated API or a report needs a total order, and a total order means every row must be distinguishable by the sort key. Adding id as a final key — ORDER BY category, unit_price DESC, id — makes the result reproducible and lets the engine use an index for the whole sort.

Two more details worth knowing. Sorting by a column that is not in the SELECT list is allowed, but it can defeat an index if the engine would otherwise have covered the scan from the index alone. And ORDER BY on a large result set is expensive: it forces a sort of every surviving row unless an index already provides the order, which is why an index on the sort key is one of the highest-value things you can add.

Text sorts lexicographically — character by character, by the code point of each character. The practical consequence is that numbers stored as text sort as strings: '10' comes before '9', because '1' has a lower code point than '9'. The same applies to versions, postcodes, phone numbers and any identifier with a numeric suffix.

The second thing that varies is case. SQLite's default BINARY collation compares raw bytes, so uppercase letters sort before lowercase ones and 'apple' comes after 'Zebra'. MySQL's default collation is usually case-insensitive and accent-insensitive, so 'apple' and 'Apple' compare equal. PostgreSQL's default depends on the locale of the database. That means the same ORDER BY can produce different row order on different engines, which is a real problem when a report has to reconcile across systems.

Third, how NULLs are placed is not universally the same. SQLite sorts NULLs first in an ascending sort and last in a descending sort. PostgreSQL puts NULLs last on ascending sorts by default and you have to say NULLS FIRST explicitly. MySQL puts NULLs first ascending. SQLite accepts explicit NULLS FIRST and NULLS LAST clauses, which is the portable way to stop depending on the default.

What to do about it in practice. Sort numbers as numbers by giving the column a numeric type rather than storing everything as text. If you must sort text case-insensitively, use ORDER BY lower(name) — and accept that the function wrapping the column stops an index from being used. If the ordering matters to the report, state it explicitly with NULLS FIRST or NULLS LAST rather than relying on the engine default.

It is allowed. ORDER BY runs after the projection, so it can reference any column of the underlying relation even if that column was not selected. This is very useful — it lets you return a lean result set and still sort by a column you did not send over the wire.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(60) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (id, name, category, unit_price) VALUES
  (1, 'Mechanical Keyboard', 'peripherals', 79.50),
  (2, 'USB-C Hub',        'peripherals', 32.00),
  (3, '27-inch Monitor',  'displays',    249.99),
  (4, 'Standing Desk',    'furniture',   419.00);

-- Sorting by a column that is not projected is perfectly legal.
SELECT name
FROM products
ORDER BY unit_price DESC
LIMIT 2;

Two rows come back — the Standing Desk at 419.00 and the 27-inch Monitor at 249.99 — even though unit_price appears nowhere in the output.

The catches are all about what the engine can still do for you. The first is DISTINCT: SELECT DISTINCT name FROM products ORDER BY unit_price DESC is rejected, because after deduplication the engine no longer knows which unit_price belongs to which surviving row. The second is set operations: in a UNION, the ORDER BY can only reference the output column names or their ordinal positions, because the intermediate result is just a list of columns with no table behind it. The third is that sorting by an unprojected column means the sort key is not in the result, so if the rows tie you have no way to break the tie from the output alone — add a tiebreaker that you do project.

The performance angle is the one that bites in production. If the engine could have satisfied the query entirely from an index on the projected columns, adding a sort on a different column may force it to read the table rows instead. Check the plan when a query with an unprojected sort key is unexpectedly slow.