SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order
SQL ORDER BY sorts query results by one or more columns, each ascending (ASC) or descending (DESC). The first column decides the order and later columns only break ties. Learn how to mix directions, control where NULLs go, ignore case, define a custom order and keep sorting fast, with queries we ran.
- SQL ORDER BY with several columns sorts by the first, then uses each next column only to break ties.
- Each column gets its own direction:
ORDER BY dept ASC, salary DESC. - Rows that tie on every listed column have no guaranteed order; add a unique column such as the id.
- Where NULLs land differs by database;
NULLS LASTor aCASEtrick makes it explicit.
Without ORDER BY, a database may return rows in any order it likes, and that order can change after an index is added, a table grows or the server is upgraded. Every report, export, paginated API and test that compares query output needs an explicit, complete sort. We ran all queries below in SQLite 3.50.4; we note where PostgreSQL, MySQL, SQL Server and Oracle behave differently, although we did not run those engines.
Quick answer
SELECT dept, name, salary
FROM emp
ORDER BY dept ASC, salary DESC, name;
Sort by department A to Z, then by salary from highest to lowest within each department, then by name to break any remaining ties. ASC is the default, so you can omit it.
Sample data
CREATE TABLE emp(id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INT, joined TEXT);
INSERT INTO emp(name, dept, salary, joined) VALUES
('Asha','QA',90,'2024-03-01'), ('ravi','Dev',120,'2023-07-15'),
('Li','QA',NULL,'2025-01-10'), ('Sam','Dev',120,'2022-11-01'),
('Noor','Ops',75,'2024-09-30'),('Émile','Dev',110,NULL),
('bob','QA',90,'2021-05-05');
The data includes a tie in salary, a NULL salary, lowercase names and an accented name, because those are exactly the cases where sorting goes wrong.
Sorting by multiple columns
SELECT dept, name, salary FROM emp ORDER BY dept ASC, salary DESC;
| dept | name | salary |
|---|---|---|
| Dev | ravi | 120 |
| Dev | Sam | 120 |
| Dev | Émile | 110 |
| Ops | Noor | 75 |
| QA | Asha | 90 |
| QA | bob | 90 |
| QA | Li | NULL |
Think of it like a phone book: sort by surname, and only look at first names when surnames match. The salary column never moved a Dev row past an Ops row; it only reordered rows inside each department. Each column takes its own ASC or DESC, and a direction applies only to the column right before it. Writing ORDER BY dept, salary DESC means dept ascending, salary descending.
Ties have no guaranteed order
Ravi and Sam both earn 120, and Asha and bob both earn 90. With ORDER BY salary DESC alone, the database may return either one first, and the order can change between runs, servers or versions. That is how pagination ends up showing the same row on two pages, and how a test that compares query output starts failing for no visible reason. Add a final tie-breaker:
SELECT name, salary FROM emp ORDER BY salary DESC, name;
('Sam', 120) ('ravi', 120) ('Émile', 110) ('Asha', 90) ('bob', 90) ('Noor', 75) ('Li', None)
Notice that Sam came before ravi. That is not a mistake; it is case sensitivity, covered below. The safest tie-breaker is the primary key, which is unique by definition. Our SQL pagination guide shows why this matters for page-by-page APIs.
Where do NULLs go?
SELECT name, salary FROM emp ORDER BY salary; -- NULL first in SQLite
SELECT name, salary FROM emp ORDER BY salary NULLS LAST; -- NULL last
SELECT name, salary FROM emp ORDER BY salary IS NULL, salary; -- works everywhere
In SQLite, NULL sorts as the smallest value, so Li appeared first in ascending order. Adding NULLS LAST moved Li to the end. Databases disagree on the default: PostgreSQL and Oracle put NULLs last in ascending order and first in descending order, while MySQL, SQL Server and SQLite treat NULL as smallest. NULLS FIRST and NULLS LAST are supported by PostgreSQL, Oracle and SQLite 3.30+, but not by MySQL or SQL Server.
The portable trick is to sort on an expression first: salary IS NULL is 0 for real values and 1 for NULLs, so it pushes NULLs to the end. In SQL Server, write CASE WHEN salary IS NULL THEN 1 ELSE 0 END, because it does not allow a bare comparison in ORDER BY. NULL handling is covered in our SQL NULL values guide.
Upper and lower case
SELECT name FROM emp ORDER BY name; -- Asha, Li, Noor, Sam, bob, ravi, Émile
SELECT name FROM emp ORDER BY name COLLATE NOCASE; -- Asha, bob, Li, Noor, ravi, Sam, Émile
SQLite’s default is binary comparison, so every capital letter sorts before every lowercase letter, and “bob” landed after “Sam”. COLLATE NOCASE fixed that, but only for plain English letters: “Émile” still came last, because NOCASE does not understand accents. Other databases depend on the column’s collation: MySQL’s default collations are usually case-insensitive, PostgreSQL follows the database locale, and SQL Server follows the server or column collation. If your data has accents or many languages, set a proper locale-aware collation, or sort in the application.
Tip: when an export “sorts wrongly”, check for lowercase, leading spaces and accents first. ORDER BY LOWER(TRIM(name)) fixes the first two, but it also stops the database using an index on name.
A custom order with CASE
SELECT name, dept FROM emp
ORDER BY CASE dept WHEN 'Ops' THEN 1 WHEN 'Dev' THEN 2 ELSE 3 END,
name COLLATE NOCASE;
Business order is rarely alphabetical: priority High, Medium, Low; status Open, In progress, Done. A CASE expression maps each value to a number and sorts by it. Our result listed Noor (Ops) first, then the Dev team, then QA, with names sorted inside each group. MySQL also offers FIELD(dept, 'Ops', 'Dev') for the same idea. For bigger lists, store a sort_order column in a lookup table instead. CASE is explained in our SQL CASE WHEN guide.
Sorting by alias, expression or position
SELECT name, salary * 12 AS yearly FROM emp
WHERE salary IS NOT NULL ORDER BY yearly DESC LIMIT 3;
SELECT dept, COUNT(*) FROM emp GROUP BY dept ORDER BY 2 DESC, 1;
ORDER BY runs after SELECT, so it can use column aliases like yearly, which WHERE cannot. You can also sort by column position, as in ORDER BY 2 DESC, 1, which gave Dev 3, QA 3, Ops 1. Positions are handy in quick ad-hoc queries but fragile in saved code: adding a column to the SELECT list silently changes the sort. Prefer names.
Numbers stored as text
SELECT v FROM (SELECT '10' v UNION ALL SELECT '9' UNION ALL SELECT '100') ORDER BY v;
-- 10, 100, 9
SELECT v FROM (SELECT '10' v UNION ALL SELECT '9' UNION ALL SELECT '100')
ORDER BY CAST(v AS INTEGER);
-- 9, 10, 100
Text sorts character by character, so the first query returned 10, 100, 9. Casting to an integer gave the expected order. This is common with imported CSV data and version numbers. The real fix is storing numbers in numeric columns; casting in ORDER BY is a workaround that also prevents index use. Text cleaning is covered in our SQL string functions guide.
ORDER BY with LIMIT: top N rows
ORDER BY ... LIMIT n returns the top n rows, as in our yearly-salary query, which returned ravi and Sam at 1,440 and Émile at 1,320. Without ORDER BY, LIMIT returns arbitrary rows, which is a common bug in “show the latest 10” features. SQL Server uses TOP n or OFFSET ... FETCH, and Oracle uses FETCH FIRST n ROWS ONLY. When ties at the cut-off matter, use ranking functions; see our RANK vs DENSE_RANK guide.
Indexes and sorting speed
CREATE INDEX ix ON emp(dept, salary DESC);
EXPLAIN QUERY PLAN SELECT * FROM emp WHERE dept = 'Dev' ORDER BY salary DESC;
-- SEARCH emp USING INDEX ix (dept=?)
EXPLAIN QUERY PLAN SELECT * FROM emp ORDER BY name;
-- SCAN emp
-- USE TEMP B-TREE FOR ORDER BY
When an index matches the filter and sort columns in order, the database reads rows already sorted and skips the sort step. The second plan shows “USE TEMP B-TREE FOR ORDER BY”, meaning SQLite had to sort the rows itself because no index covered name. On small tables this is instant; on millions of rows it is often the slowest part of a query. Our SQL indexes guide explains how to read these plans.
Differences between databases
| Feature | SQLite | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|---|
| NULLs in ASC | First | Last | First | First |
| NULLS FIRST/LAST | Yes (3.30+) | Yes | No | No |
| Default text order | Binary, case-sensitive | Locale collation | Usually case-insensitive | Server collation |
| Top N | LIMIT | LIMIT | LIMIT | TOP / FETCH |
Only the SQLite column was verified by running queries; the others reflect each database’s documented defaults.
Common mistakes
- Relying on default order. Without ORDER BY, row order is not guaranteed.
- Thinking DESC applies to every column. It applies only to the column before it.
- No tie-breaker. Add a unique column so the order is repeatable.
- Assuming NULL position. Make it explicit.
- Sorting numbers stored as text. Fix the column type.
- Ordering in a subquery or view. Outer queries may ignore it; sort in the final query.
Final thoughts
SQL ORDER BY is simple until real data arrives with ties, NULLs, mixed case and numbers stored as text. List columns from most to least important, give each its own direction, finish with a unique tie-breaker, decide where NULLs belong, and match your collation to your users’ language. Add an index that matches the sort for large tables, and your results will be both correct and fast. For aggregated sorting, see our GROUP BY guide.
Frequently asked questions
How do I sort by multiple columns in SQL?
List the columns after ORDER BY separated by commas, for example ORDER BY dept, salary DESC. The second column only orders rows that tie on the first.
Can I use ASC and DESC in the same ORDER BY?
Yes. Each column takes its own direction, such as ORDER BY dept ASC, salary DESC. A direction applies only to the column it follows.
How do I put NULL values last in SQL?
Use NULLS LAST in PostgreSQL, Oracle or SQLite. In MySQL and SQL Server, sort by an expression first, such as ORDER BY salary IS NULL, salary or a CASE expression.
How do I sort in a custom order in SQL?
Use a CASE expression that maps each value to a number, for example ORDER BY CASE status WHEN ‘Open’ THEN 1 WHEN ‘Done’ THEN 2 END.
Why does my ORDER BY return a different order each time?
Rows that tie on every ORDER BY column have no guaranteed order. Add a unique tie-breaker such as the primary key to make the result repeatable.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.