ORDER BY

A result set has no order until ORDER BY gives it one: sort keys, per-key direction, and MySQL's fixed NULL placement.

SyntaxORDER BY {col_name | expr | position} [ASC | DESC] [, ...]

A table is an unordered set, and so is the result of a query. Whatever order rows arrive in without ORDER BY is a side effect of the plan the optimizer happened to choose — an index scan today, a different join order after the next ANALYZE TABLE — and it is not a promise. If order matters, state it.

Keys and directions

ORDER BY takes a list of sort keys. Each key may be a column name, an expression, a select-list alias, or a column position, though positions are deprecated because the syntax was removed from the SQL standard. Each key carries its own direction: ASC is the default, DESC reverses it, and later keys break ties in earlier ones. Since MySQL 8.0 index key parts can themselves be descending, so a mixed sort such as ORDER BY a DESC, b ASC can be satisfied by an index rather than a filesort, provided the index has matching homogeneity.

Where the NULLs go

MySQL presents NULL values first with ORDER BY ... ASC and last with ORDER BY ... DESC. There is no NULLS FIRST or NULLS LAST clause in MySQL 8.4. To override the default, sort on a nullness test first: ORDER BY col IS NULL, col puts NULLs last in an ascending sort, because col IS NULL returns 0 for a value and 1 for NULL.

Example

Example · sql
SELECT id, status, priority, due_at
FROM   tickets
WHERE  status <> 'closed'
ORDER BY
    priority   DESC,   -- highest priority first
    due_at IS NULL,    -- 0 sorts before 1: dated tickets first, undated last
    due_at     ASC,    -- ASC is the default; spelled out for the next reader
    id         ASC;    -- unique tiebreaker: makes the order fully deterministic

When to use it

  • Producing a stable page order for an API list endpoint.
  • Showing unscheduled or unpriced records at the end of a list rather than the top.
  • Presenting statuses or tiers in business order instead of alphabetical order.

More examples

Three ways to name the same sort key

The alias form is what to write. The position form is worth recognising because it is everywhere in older scripts, and because it is the reason a reordered select list can quietly change a report's sort.

Example · sql
-- Repeat the expression: verbose, but works everywhere.
SELECT CONCAT(last_name, ', ', first_name) AS full_name
FROM customers ORDER BY CONCAT(last_name, ', ', first_name);

-- Use the select-list alias: ORDER BY may refer to it (WHERE may not).
SELECT CONCAT(last_name, ', ', first_name) AS full_name
FROM customers ORDER BY full_name;

-- Use the column position, 1-based. Deprecated: the syntax was removed from
-- the SQL standard, and it silently re-sorts if someone edits the select list.
SELECT CONCAT(last_name, ', ', first_name) AS full_name
FROM customers ORDER BY 1;

Putting NULLs where you want them

Needed whenever "no value" should not be treated as "smallest value" — a nullable due date, an optional rank, a price that has not been set. Since MySQL 8.4 has no NULLS LAST, the IS NULL key is the idiom.

Example · sql
SELECT name, retired_at FROM products ORDER BY retired_at ASC;   -- NULLs first
SELECT name, retired_at FROM products ORDER BY retired_at DESC;  -- NULLs last

-- Ascending by date, but with the still-active (NULL) products at the end.
SELECT name, retired_at
FROM   products
ORDER BY retired_at IS NULL ASC,  -- 0 for real dates, 1 for NULL
         retired_at ASC;

Sorting by a business order, not an alphabet

Both defeat the index and sort in memory, so keep them for small result sets or add the rank as a column. The CASE form is safer precisely because its ELSE branch makes the fallback explicit.

Example · sql
-- Explicit and readable: CASE gives each status a sort rank.
SELECT id, status, placed_at
FROM   orders
ORDER BY CASE status
           WHEN 'failed'  THEN 0
           WHEN 'pending' THEN 1
           WHEN 'paid'    THEN 2
           ELSE 3
         END,
         placed_at DESC;

-- Compact alternative: FIELD() returns the 1-based position of its first
-- argument in the list that follows, and 0 when it is not found -- so any
-- status you forgot to list sorts FIRST, not last.
SELECT id, status FROM orders
ORDER BY FIELD(status, 'failed', 'pending', 'paid');

Related

sql limit offsetsql nullsql casesql index introsql row number

Discussion

  • Be the first to comment on this lesson.