How does ORDER BY work with multiple columns, and what is the default sort direction?
When you list multiple columns in ORDER BY, SQL sorts by the first column, then breaks ties using the second, and so on. The default direction is ASC (ascending). Each column gets its own independent ASC or DESC modifier.
How to think about it
This is a warm-up that checks SQL fundamentals — how multiple sort keys interact, how ASC/DESC scope works per column, and how NULLs sort (the part that quietly trips people up).
ORDER BY sorts by the first column; when two rows tie there, the second column breaks the tie, and so on. The default direction is ASC, and each direction modifier applies only to the column immediately before it — DESC on one column does not cascade to the next. That last point is the common misread.
A worked example
Sort by department alphabetically, then highest earner first within each department:
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;| name | department | salary |
|---|---|---|
| Aarav | Eng | 120000 |
| Bea | Eng | 95000 |
| Chen | Eng | 95000 |
| Farah | HR | 65000 |
| Gao | HR | NULL |
| Dara | Sales | 80000 |
| Eli | Sales | 72000 |
Read it in two passes: departments come out Eng → HR → Sales (the primary ASC key), and inside each, salary descends. salary DESC never touches the department ordering — the two keys are independent. Note where Gao’s NULL salary lands: last within HR under DESC.
The NULL gotcha
NULL has no natural position in a sort, so engines disagree:
- PostgreSQL, BigQuery — NULL sorts as the largest value: last in
ASC, first inDESC. - MySQL, SQLite — NULL sorts as the smallest value: first in
ASC, last inDESC(exactly what put Gao at the bottom of HR above).
When it matters, state it explicitly — PostgreSQL accepts ORDER BY salary DESC NULLS LAST. You can also sort by column position (ORDER BY 3 DESC, 1 ASC), but it’s brittle: reorder the SELECT list and the sort silently changes, so prefer column names in production.