ORDER BY Multiple Columns in SQL

Sort query results by more than one column to control exactly how ties are broken and groups are ordered.

When a Single Sort Column Is Not Enough

You are building a product report grouped by category. Within each category you want the most expensive items at the top. Sorting by category_id alone gives you the grouping but scrambles the prices. Sorting by price alone loses the grouping entirely. Multi-column sorting lets you do both at the same time, cleanly and in one query.

How Multi-Column ORDER BY Works

You list columns after ORDER BY separated by commas. SQL sorts the entire result by the first column. Within any group of rows that share the same first-column value, it then sorts by the second column. The third column only matters when both the first and second columns tie, and so on.

Key idea: Think of multi-column sorting as a priority list. The first column is the primary sort key. Every column after it is a tiebreaker for the one before it.

-- Products sorted by category first, then by price descending within each category
SELECT product_id, product_name, category_id, price
FROM products p
ORDER BY category_id ASC, price DESC;

-- Customers sorted by state, then alphabetically by last name within each state
SELECT customer_id, first_name, last_name, city, state
FROM customers c
ORDER BY state ASC, last_name ASC;

Mixing ASC and DESC Across Columns

Each column in your ORDER BY list takes its own direction keyword. You are not locked into one direction for the whole clause.

-- Employees sorted by department ascending, then salary descending within each department
SELECT employee_id, first_name, last_name, department, salary
FROM employees e
ORDER BY department ASC, salary DESC;

-- Orders sorted by status ascending, then by total_amount descending within each status
SELECT order_id, customer_id, status, total_amount, order_date
FROM orders o
ORDER BY status ASC, total_amount DESC;

This pattern is useful whenever you want to group by a category and rank items within that group from highest to lowest.

Three-Column Sorts

Sometimes two columns are still not enough to produce a deterministic order. Add a third column as a final tiebreaker.

-- Customers sorted by country, then state, then city
SELECT customer_id, first_name, last_name, city, state, country
FROM customers c
ORDER BY country ASC, state ASC, city ASC;

-- Orders sorted by status, then order_date descending, then total_amount descending
SELECT order_id, customer_id, status, order_date, total_amount
FROM orders o
ORDER BY status ASC, order_date DESC, total_amount DESC;

Multi-Column Sorting After Aggregation

ORDER BY with multiple columns works just as well after a GROUP BY. You can sort by grouped columns and aggregate results together.

-- Total revenue per status per shipping state, sorted by state then revenue descending
SELECT shipping_state, status, SUM(total_amount) AS revenue
FROM orders o
GROUP BY shipping_state, status
ORDER BY shipping_state ASC, revenue DESC;

-- Count of products per brand per category, sorted by category then count descending
SELECT category_id, brand, COUNT(product_id) AS product_count
FROM products p
WHERE brand IS NOT NULL
GROUP BY category_id, brand
ORDER BY category_id ASC, product_count DESC;

Common Mistakes

-- wrong
SELECT product_id, product_name, category_id, price
FROM products p
ORDER BY category_id, price DESC ASC;

You cannot attach two direction keywords to a single column. Each column gets one direction. This will throw a syntax error.

-- correct
SELECT product_id, product_name, category_id, price
FROM products p
ORDER BY category_id ASC, price DESC;

---

-- wrong
SELECT customer_id, first_name, last_name, state
FROM customers c
ORDER BY last_name ASC, state ASC;

The column order here sorts alphabetically by last name first, then uses state as a tiebreaker. If the goal was to group by state and sort names within each state, the columns are in the wrong order.

-- correct
SELECT customer_id, first_name, last_name, state
FROM customers c
ORDER BY state ASC, last_name ASC;

---

-- wrong
SELECT order_id, customer_id, status, total_amount
FROM orders o
ORDER BY status, DESC total_amount;

The direction keyword must come after the column name, not before it. This is a syntax error.

-- correct
SELECT order_id, customer_id, status, total_amount
FROM orders o
ORDER BY status ASC, total_amount DESC;

Quick Recap

List columns after ORDER BY separated by commas to sort by more than one column
SQL applies the sort left to right, using each column as a tiebreaker for the previous one
Each column in the list gets its own ASC or DESC keyword independently
The order you list the columns determines priority, so put the most important sort key first
Multi-column sorting works after GROUP BY as well, including on aggregate expressions

Exercise 1 Easy

List all customers sorted by country ascending, then by last_name ascending within each country. Return customer_id, first_name, last_name, and country.

Hint: Put country as the first sort key and last_name as the second, both ASC.

Solution

SELECT customer_id, first_name, last_name, country FROM customers c ORDER BY country ASC, last_name ASC;

Exercise 2 Medium

Find all active products (is_active = 1) where stock_qty is greater than 0. Return product_id, product_name, category_id, price, and stock_qty. Sort by category_id ascending, then by price descending within each category, then by stock_qty descending as a final tiebreaker.

Hint: Filter with WHERE first, then build an ORDER BY clause with three columns and their individual directions.

Solution

SELECT product_id, product_name, category_id, price, stock_qty FROM products p WHERE is_active = 1 AND stock_qty > 0 ORDER BY category_id ASC, price DESC, stock_qty DESC;

Exercise 3 Hard

For each employee, count the total number of orders they handled. Return employee_id, first_name, last_name, department, and order_count. Sort by department ascending, then by order_count descending within each department. Include only employees who have handled at least one order.

Hint: JOIN employees to orders on employee_id, GROUP BY employee columns, filter with HAVING, then use a two-column ORDER BY.

Solution

SELECT e.employee_id, e.first_name, e.last_name, e.department, COUNT(o.order_id) AS order_count FROM employees e JOIN orders o ON e.employee_id = o.employee_id GROUP BY e.employee_id, e.first_name, e.last_name, e.department HAVING COUNT(o.order_id) >= 1 ORDER BY e.department ASC, order_count DESC;

Frequently Asked Questions

Can you ORDER BY multiple columns in SQL?

Yes. List the columns in `ORDER BY` separated by commas. SQL sorts by the first column, then uses each subsequent column to break ties.

Can each column in ORDER BY have a different sort direction?

Yes. Each column in the list can independently be `ASC` or `DESC`. For example, `ORDER BY state ASC, total_amount DESC` is valid.

Does the order of columns in ORDER BY matter?

Yes. SQL applies the sort left to right. The first column is the primary sort. Later columns only matter when two rows have the same value in the earlier columns.

How many columns can you sort by in SQL ORDER BY?

There is no practical limit. You can list as many columns as you need, though in practice two or three is usually enough.