How does ORDER BY work with multiple columns, and what is the default sort direction?

How to think about it

What the question is really testing

This is a warm-up question that checks SQL fundamentals — specifically whether you understand how multiple sort keys interact, how ASC/DESC scope works per column, and how NULLs sort (which trips up many people).

The sorting model: left-to-right priority

ORDER BY sorts by the first column. When two rows tie on the first column, the second column breaks the tie. And so on for each subsequent column.

SELECT employee_id, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This groups employees alphabetically by department. Within each department, the highest earner appears first. Each column independently carries its own direction.

Mixing ASC and DESC

Each direction modifier applies only to the column immediately before it:

-- Most recent orders first; within the same date, cheapest first
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, total_amount ASC;

DESC on order_date does not affect total_amount. This is a common misread — people sometimes think DESC applies to all subsequent columns.

Try it live

NULL sort order — the one gotcha

NULLs have no defined position in the natural sort order, so databases differ:

• PostgreSQL, BigQuery: NULLs sort as the largest value — appear last in ASC, first in DESC.
• MySQL / SQLite: NULLs sort as the smallest value — appear first in ASC, last in DESC.

Control it explicitly in PostgreSQL:

ORDER BY salary DESC NULLS LAST;

Ordering by column position (convenient but fragile)

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY 3 DESC, 1 ASC;  -- same as: ORDER BY hire_date DESC, first_name ASC

This works but is brittle. If you reorder the SELECT list, the sort silently changes. Prefer column names in production code.