Multiple Sort Keys

Definition

You can sort by more than one column by listing them, comma-separated, in ORDER BY. The first column is the primary sort key; each subsequent column is only used to break ties within groups of equal values from the column(s) before it. Each column can independently be ASC or DESC.

sql
SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Worked Example

Using products(id, name, category, price, rating):

sql
INSERT INTO products (id, name, category, price, rating) VALUES
 (6, 'Phone Case', 'Electronics', 299.00, 4.1),
 (7, 'Table Lamp', 'Home', 699.00, 4.3);

Sort by category, then within each category by price ascending:

sql
SELECT name, category, price
FROM products
ORDER BY category, price;

Result:

 name               | category    | price
--------------------+-------------+--------
 Phone Case         | Electronics | 299.00
 Wireless Mouse     | Electronics | 599.00
 Bluetooth Speaker  | Electronics | 1499.00
 Table Lamp         | Home        | 699.00
 Desk Lamp          | Home        | 899.00
 Notebook Set       | Stationery  | 199.00
 Yoga Mat           | Sports      | 499.00

Notice: price only sorted the rows inside each category group (e.g. Phone Case before Mouse before Speaker, all Electronics). It never reordered across categories — categories themselves are ordered alphabetically because that is the first key.

Column Order Matters

ORDER BY category, price and ORDER BY price, category produce completely different results:

sql
SELECT name, category, price FROM products ORDER BY price, category;

This sorts the entire table by price first (199, 299, 499, 599, 699, 899, 1499), and category is now irrelevant unless two rows happen to have the exact same price. The two queries answer different questions: "group by category, cheapest first within each" vs. "cheapest overall, ignoring category."

Mixed Directions

sql
SELECT name, category, rating
FROM products
ORDER BY category ASC, rating DESC;

This groups alphabetically by category, then shows the highest-rated product first within each category — a common "best in category" pattern.

Key Takeaways

• Q: What does the second column in `ORDER BY col1, col2` actually do?

A: It only breaks ties for rows that have identical col1 values; it has zero effect on rows whose col1 values already differ.

• Q: Does swapping the column order change the result?

A: Yes — the sort keys are applied in strict left-to-right priority, so swapping changes which column dominates.

• Q: Can each column have its own direction?

A: Yes, e.g. ORDER BY category ASC, price DESC.