1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL ORDER BY multiple columns explained

Key takeaways

  • 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 LAST or a CASE trick 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →