Introduction

Sorting by more than one column is one of the most common SQL tasks because real datasets usually need tie-breakers. A report might be grouped by department, then alphabetized by last name, then sorted by hire date inside those groups. SQL handles this with a single ORDER BY clause, but the order of columns and the handling of NULL values matter more than many people expect.

How Multiple-Column Ordering Works

SQL evaluates the ORDER BY list from left to right. The first column is the primary sort key. If two rows tie on that column, SQL compares the second column. If they still tie, it compares the third, and so on.

Basic syntax:

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

This means:

1. group rows by department alphabetically
2. within each department, sort by last_name
3. if two people share the same last name, put the higher salary first

That left-to-right precedence is the core rule to remember.

A Practical Example

Suppose you want to list orders so the newest orders for each customer appear first, while customers themselves are sorted alphabetically.

SELECT customer_name, order_id, order_date, total_amount
FROM orders
ORDER BY customer_name ASC, order_date DESC;

If the data contains:

Alice   101   2025-01-02
Alice   109   2025-03-01
Bob     110   2025-02-10

the result order becomes:

Alice   109   2025-03-01
Alice   101   2025-01-02
Bob     110   2025-02-10

The first key keeps all of Alice’s rows together. The second key determines the order inside Alice’s group.

Mix Ascending and Descending Safely

Each sort key can have its own direction.

SELECT product_name, category, rating, price
FROM products
ORDER BY category ASC, rating DESC, price ASC;

This is a common reporting pattern:

• group by category
• show highest-rated items first
• break remaining ties by lower price

Do not assume DESC applies to all later columns. It only applies to the column it directly follows.

Use Extra Tie-Breakers for Stable Results

If the ordered columns still contain duplicates, row order may be nondeterministic from one execution to another. That is not always wrong, but it can make pagination and testing harder.

Example:

SELECT id, created_at, status
FROM jobs
ORDER BY status, created_at DESC;

If two rows share the same status and identical created_at, their relative order is unspecified. A safer query adds a final unique key:

SELECT id, created_at, status
FROM jobs
ORDER BY status, created_at DESC, id ASC;

That final tie-breaker makes the output stable.

NULL Handling Differs Across Databases

NULL sorting is one of the most database-specific parts of ordering. Some engines sort NULL values first in ascending order, others last. PostgreSQL and Oracle support explicit control:

SELECT first_name, last_login_at
FROM users
ORDER BY last_login_at DESC NULLS LAST;

If your database does not support NULLS FIRST or NULLS LAST, use an expression:

1SELECT first_name, last_login_at
2FROM users
3ORDER BY
4  CASE WHEN last_login_at IS NULL THEN 1 ELSE 0 END,
5  last_login_at DESC;

Be careful here. A query that looks fine on one database can produce a different order on another because of NULL behavior alone.

Performance and Index Considerations

Sorting is not just a presentation concern. It also affects performance. When the ORDER BY columns line up with an index, the database may be able to avoid an expensive explicit sort.

For example, this index may help:

CREATE INDEX idx_orders_customer_date
ON orders (customer_name, order_date DESC);

That does not guarantee the index will be used, because filtering, joins, and query shape still matter, but it gives the optimizer a useful option.

As a rule:

• sort only by columns you actually need
• add deterministic tie-breakers when output order matters
• review execution plans for expensive sorts on large tables

Common Pitfalls

The first common mistake is assuming the second ordered column affects the whole result set. It only breaks ties created by the first column.

Another issue is forgetting about nondeterministic order when ties remain. This often shows up in paginated APIs, where records can appear on different pages between requests.

NULL handling is also a major source of bugs, especially when SQL moves between PostgreSQL, MySQL, SQL Server, and SQLite.

Finally, do not use column positions such as ORDER BY 1, 3 DESC unless there is a strong reason. It is legal SQL, but it becomes harder to read and easier to break during refactoring.

Summary

• SQL applies multiple sort keys from left to right.
• Each column in ORDER BY can have its own ASC or DESC direction.
• Add a unique final tie-breaker when you need stable, deterministic output.
• Be explicit about NULL ordering when portability matters.
• Matching indexes can make large ordered queries much faster.