ORDER BY multiple columns in MySQL - Step-by-Step Execution

Concept Flow - ORDER BY multiple columns

Start Query

↓

Fetch Rows

↓

Sort by 1st Column

↓

If tie in 1st Column

Yes↓

Sort by 2nd Column

↓

Return Sorted Rows

↓

End

The database fetches rows, sorts them first by the first column, then if there are ties, sorts those tied rows by the second column, and returns the sorted result.

Execution Sample

MySQL

SELECT name, age, city
FROM people
ORDER BY city ASC, age DESC;

This query sorts people first by city alphabetically, then by age from oldest to youngest within each city.

Execution Table

Step	Action	Rows State (name, age, city)	Sorting Focus	Resulting Order
1	Fetch all rows	[('Alice', 30, 'Boston'), ('Bob', 25, 'Austin'), ('Carol', 22, 'Boston'), ('Dave', 35, 'Austin')]	None	Original order
2	Sort by city ASC	[('Bob', 25, 'Austin'), ('Dave', 35, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	city	Austin rows first, then Boston
3	Tie detected in city 'Austin'	[('Bob', 25, 'Austin'), ('Dave', 35, 'Austin')]	age DESC	Sort Austin rows by age descending
4	Sort Austin rows by age DESC	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin')]	age DESC	Dave before Bob
5	Tie detected in city 'Boston'	[('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	age DESC	Sort Boston rows by age descending
6	Sort Boston rows by age DESC	[('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	age DESC	Alice before Carol
7	Combine sorted groups	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	Final	Rows sorted by city ASC, age DESC
8	Return sorted rows	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	Final	Query ends

💡 All rows sorted by city ascending, then by age descending within each city

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	Final
Rows	[('Alice', 30, 'Boston'), ('Bob', 25, 'Austin'), ('Carol', 22, 'Boston'), ('Dave', 35, 'Austin')]	[('Bob', 25, 'Austin'), ('Dave', 35, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]

Key Moments - 2 Insights

Why do we sort by the second column only after sorting by the first column?

Because ORDER BY sorts first by the first column, and only if there are ties (same values) in that column, it uses the second column to decide order. This is shown in execution_table rows 3 and 5 where ties trigger sorting by age.

What happens if the second column sorting order is DESC but the first is ASC?

The first column sorts ascending overall, but within groups of the same first column value, the second column sorts descending. This is shown in rows 4 and 6 where age is sorted descending within each city.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the order of rows after sorting by city ASC?

A [('Alice', 30, 'Boston'), ('Carol', 22, 'Boston'), ('Bob', 25, 'Austin'), ('Dave', 35, 'Austin')]

B [('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Carol', 22, 'Boston'), ('Alice', 30, 'Boston')]

C [('Bob', 25, 'Austin'), ('Dave', 35, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]

D [('Carol', 22, 'Boston'), ('Alice', 30, 'Boston'), ('Dave', 35, 'Austin'), ('Bob', 25, 'Austin')]

💡 Hint

Check the 'Rows State' column at step 2 in execution_table

At which step does the sorting by age DESC within city 'Austin' happen?

A Step 2

B Step 4

C Step 6

D Step 7

💡 Hint

Look for 'Sort Austin rows by age DESC' in the Action column of execution_table

If we changed ORDER BY city ASC, age DESC to ORDER BY city DESC, age ASC, how would the final order change?

A Cities would be sorted descending, and ages ascending within each city

B Cities and ages would both be sorted ascending

C Cities would be sorted ascending, ages descending

D No change in order

💡 Hint

ORDER BY clause controls sorting direction per column, see concept_flow and execution_table

Concept Snapshot

ORDER BY multiple columns syntax:
SELECT columns FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Rows are sorted first by column1.
If values tie, column2 sorts those tied rows.
Each column can have its own ASC or DESC order.

Full Transcript

This visual execution shows how MySQL sorts rows when using ORDER BY with multiple columns. First, all rows are fetched. Then the database sorts rows by the first column in the specified order. If multiple rows have the same value in the first column, those rows are sorted by the second column. This process continues for any additional columns if specified. The example query sorts people by city alphabetically, then by age descending within each city. The execution table traces each step, showing how rows reorder after sorting by city, then by age within tied cities. The variable tracker shows the rows' state after each sorting step. Key moments clarify why sorting happens in this order and how ASC and DESC affect sorting. The quiz tests understanding of the sorting steps and effects of changing sort directions. The snapshot summarizes the syntax and behavior of ORDER BY with multiple columns.