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

Concept Flow - ORDER BY multiple columns

Start Query

↓

Fetch Rows

↓

Sort by First Column

↓

Sort by Second Column (if tie)

↓

Return Sorted Result

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

Execution Sample

SQL

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	Intermediate Result	Explanation
1	Fetch all rows	[('Alice', 30, 'Boston'), ('Bob', 25, 'Austin'), ('Carol', 22, 'Boston'), ('Dave', 35, 'Austin')]	All rows retrieved from table
2	Sort by city ASC	[('Bob', 25, 'Austin'), ('Dave', 35, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	Rows ordered alphabetically by city
3	Sort by age DESC within city	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	Within each city, rows sorted by age descending
4	Return final sorted rows	[('Dave', 35, 'Austin'), ('Bob', 25, 'Austin'), ('Alice', 30, 'Boston'), ('Carol', 22, 'Boston')]	Final output after multi-column ordering

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

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
rows	empty	[('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')]

Key Moments - 2 Insights

Why does the order change after sorting by the second column?

Because the second column sorting only affects rows that have the same value in the first column, as shown in step 3 of the execution_table where age sorts rows within each city.

What happens if we reverse the order of columns in ORDER BY?

The sorting priority changes: the first column listed is sorted first, then the second. Changing the order changes the final sorted result, as the first column has higher priority.

Visual Quiz - 3 Questions

Look at the execution_table, what is the order of rows after step 2?

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

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

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

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

💡 Hint

Check the 'Intermediate Result' column for step 2 in the execution_table.

At which step does the sorting by the second column happen?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look for the step mentioning sorting by age DESC within city in the execution_table.

If we change ORDER BY to 'age DESC, city ASC', what changes in the variable_tracker?

A Rows are first sorted by city, then by age within city

B Rows remain sorted by city ascending, age descending

C Rows are first sorted by age descending, then by city ascending within same age

D Rows are sorted randomly

💡 Hint

The first column in ORDER BY has the highest sorting priority, check variable_tracker for sorting order.

Concept Snapshot

ORDER BY multiple columns sorts query results by the first column listed, then by the second column if there are ties.
Syntax: ORDER BY column1 ASC|DESC, column2 ASC|DESC
First column has higher priority.
Useful to organize data by multiple criteria.
Example: ORDER BY city ASC, age DESC sorts by city alphabetically, then age descending within city.

Full Transcript

This visual execution shows how SQL orders rows when using ORDER BY with multiple columns. First, all rows are fetched from the table. Then, the rows are sorted by the first column specified in the ORDER BY clause. If there are rows with the same value in that first column, those rows are further sorted by the second column. The example query sorts people by city alphabetically, then by age descending within each city. The execution table traces each step, showing how the rows change order after sorting by each column. The variable tracker records the state of the rows after each step. Key moments clarify why the second column sorting only affects ties and how changing the order of columns changes the final result. The quiz tests understanding of the sorting steps and effects of changing column order. The snapshot summarizes the syntax and behavior of ORDER BY with multiple columns.