ORDER BY single column in MySQL - Step-by-Step Execution

Concept Flow - ORDER BY single column

Start Query

↓

Fetch Rows from Table

↓

Sort Rows by Column

↓

Return Sorted Rows

↓

End Query

The query fetches all rows, sorts them by one column, then returns the sorted list.

Execution Sample

MySQL

SELECT name, age FROM users ORDER BY age;

This query selects names and ages from users and sorts the results by age in ascending order.

Execution Table

Step	Action	Data State	Result
1	Fetch all rows from users	[{name: 'Alice', age: 30}, {name: 'Bob', age: 25}, {name: 'Carol', age: 35}]	All rows fetched unsorted
2	Sort rows by age ascending	[{name: 'Bob', age: 25}, {name: 'Alice', age: 30}, {name: 'Carol', age: 35}]	Rows sorted by age
3	Return sorted rows	[{name: 'Bob', age: 25}, {name: 'Alice', age: 30}, {name: 'Carol', age: 35}]	Query result returned
4	End query	N/A	Execution complete

💡 All rows sorted by age ascending, query ends

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
rows	empty	[{name: 'Alice', age: 30}, {name: 'Bob', age: 25}, {name: 'Carol', age: 35}]	[{name: 'Bob', age: 25}, {name: 'Alice', age: 30}, {name: 'Carol', age: 35}]	[{name: 'Bob', age: 25}, {name: 'Alice', age: 30}, {name: 'Carol', age: 35}]

Key Moments - 2 Insights

Why does the order of rows change after step 2?

Because the ORDER BY clause sorts the rows by the specified column (age), changing their order from the original fetch.

What if two rows have the same age? How does ORDER BY handle that?

Rows with the same age keep their relative order from the original fetch (stable sort), unless another column is specified for sorting.

Visual Quiz - 3 Questions

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

A [{Bob,25}, {Alice,30}, {Carol,35}]

B [{Alice,30}, {Bob,25}, {Carol,35}]

C [{Carol,35}, {Alice,30}, {Bob,25}]

D [{Bob,25}, {Carol,35}, {Alice,30}]

💡 Hint

Check the 'Data State' column in step 2 of the execution table.

At which step does the query return the sorted rows?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for the step where 'Return sorted rows' is the action.

If we change ORDER BY age to ORDER BY age DESC, how would the order after step 2 change?

A [{Bob,25}, {Alice,30}, {Carol,35}]

B [{Carol,35}, {Alice,30}, {Bob,25}]

C [{Alice,30}, {Bob,25}, {Carol,35}]

D [{Carol,35}, {Bob,25}, {Alice,30}]

💡 Hint

Descending order means highest age first; check the sorted list in step 2.

Concept Snapshot

ORDER BY single column:
- Syntax: SELECT columns FROM table ORDER BY column_name;
- Sorts rows by one column in ascending order by default.
- Use DESC for descending order.
- Returns all rows sorted by that column.
- Sorting happens after fetching rows.

Full Transcript

This visual execution shows how a SQL query with ORDER BY on a single column works. First, all rows are fetched from the table. Then, the rows are sorted by the specified column in ascending order by default. Finally, the sorted rows are returned as the query result. The variable 'rows' changes from unsorted to sorted after the ORDER BY step. This helps beginners see how the sorting affects the data step-by-step.

Practice

1. What does the ORDER BY clause do in a MySQL query?

easy

A. It sorts the query results by one or more columns.

B. It filters rows based on a condition.

C. It groups rows with the same values.

D. It limits the number of rows returned.

Solution

1. Step 1: Understand the purpose of ORDER BY

   The ORDER BY clause is used to sort the results of a query based on one or more columns.

2. Step 2: Compare with other clauses

   Filtering rows is done by WHERE, grouping by GROUP BY, and limiting rows by LIMIT. So, only sorting matches ORDER BY.

3. Final Answer:

   It sorts the query results by one or more columns. -> Option A

4. Quick Check:

   ORDER BY = Sort results [OK]

Hint: ORDER BY sorts rows; WHERE filters rows [OK]

Common Mistakes:

• Confusing ORDER BY with WHERE clause
• Thinking ORDER BY filters rows
• Mixing ORDER BY with GROUP BY

2. Which of the following is the correct syntax to sort the table employees by the column salary in descending order?

easy

A. SELECT * FROM employees ORDER BY salary DOWN;

B. SELECT * FROM employees SORT BY salary DESC;

C. SELECT * FROM employees ORDER salary DESC;

D. SELECT * FROM employees ORDER BY salary DESC;

Solution

1. Step 1: Recall correct ORDER BY syntax

   The correct syntax to sort results is ORDER BY column_name ASC|DESC. Here, DESC means descending order.

2. Step 2: Check each option

   SELECT * FROM employees ORDER BY salary DESC; uses correct syntax. Options B, C, and D use invalid keywords or syntax.

3. Final Answer:

   SELECT * FROM employees ORDER BY salary DESC; -> Option D

4. Quick Check:

   ORDER BY + DESC = correct syntax [OK]

Hint: Use ORDER BY column DESC for descending sort [OK]

Common Mistakes:

• Using SORT BY instead of ORDER BY
• Omitting BY after ORDER
• Using DOWN instead of DESC

3. Given the table products with columns id and price, what will be the order of id after running this query?

SELECT id FROM products ORDER BY price ASC;

Assume the data:
id: 1, price: 50
id: 2, price: 20
id: 3, price: 30

medium

A. [1, 3, 2]

B. [2, 3, 1]

C. [3, 2, 1]

D. [1, 2, 3]

Solution

1. Step 1: Understand ORDER BY price ASC

   The query sorts rows by price in ascending order (lowest to highest).

2. Step 2: Sort the data by price

   Prices sorted ascending: 20 (id 2), 30 (id 3), 50 (id 1). So the id order is [2, 3, 1].

3. Final Answer:

   [2, 3, 1] -> Option B

4. Quick Check:

   Sort by price ASC = [2, 3, 1] [OK]

Hint: Sort by column values ascending to find order [OK]

Common Mistakes:

• Confusing ASC with DESC order
• Sorting by id instead of price
• Ignoring the ORDER BY clause

4. Identify the error in this query:

SELECT name FROM customers ORDER salary DESC;

medium

A. ORDER BY must come before SELECT

B. DESC cannot be used with ORDER

C. Missing BY keyword after ORDER

D. Missing WHERE clause

Solution

1. Step 1: Check ORDER BY syntax

   The correct syntax requires the keyword BY after ORDER. The query uses ORDER salary DESC which is invalid.

2. Step 2: Confirm other parts are correct

   DESC is valid with ORDER BY. No WHERE clause is needed for sorting. ORDER BY comes after SELECT.

3. Final Answer:

   Missing BY keyword after ORDER -> Option C

4. Quick Check:

   ORDER BY needs BY keyword [OK]

Hint: Always write ORDER BY, not just ORDER [OK]

Common Mistakes:

• Omitting BY after ORDER
• Using ORDER before SELECT
• Confusing DESC usage

5. You have a table students with columns name and score. You want to list all students sorted by their score from highest to lowest, but if two students have the same score, sort those students alphabetically by name ascending. Which query achieves this?

hard

A. SELECT * FROM students ORDER BY score DESC, name ASC;

B. SELECT * FROM students ORDER BY name ASC, score DESC;

C. SELECT * FROM students ORDER BY score ASC, name DESC;

D. SELECT * FROM students ORDER BY score DESC;

Solution

1. Step 1: Understand sorting requirements

   Primary sort is by score descending (highest first). Secondary sort for ties is by name ascending (alphabetical).

2. Step 2: Match query with requirements

   SELECT * FROM students ORDER BY score DESC, name ASC; orders by score DESC then name ASC, matching the requirement. SELECT * FROM students ORDER BY name ASC, score DESC; reverses order priorities. SELECT * FROM students ORDER BY score ASC, name DESC; sorts score ascending and name descending, which is wrong. SELECT * FROM students ORDER BY score DESC; misses secondary sort.

3. Final Answer:

   SELECT * FROM students ORDER BY score DESC, name ASC; -> Option A

4. Quick Check:

   ORDER BY score DESC, name ASC = correct [OK]

Hint: Use multiple columns in ORDER BY for tie-breakers [OK]

Common Mistakes:

• Reversing sort order priorities
• Missing secondary sort column
• Using ASC when DESC needed