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

Concept Flow - ORDER BY single column

Start Query

↓

Fetch Rows

↓

Sort Rows by Column

↓

Return Sorted Rows

↓

End Query

The query fetches rows from a table, sorts them by one column, then returns the sorted rows.

Execution Sample

SQL

SELECT name, age FROM people ORDER BY age;

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

Execution Table

Step	Action	Rows Before	Sort Column	Rows After	Notes
1	Start query execution	N/A	N/A	N/A	Query begins
2	Fetch all rows from people	[{"name":"Alice","age":30},{"name":"Bob","age":25},{"name":"Carol","age":35}]	age	[{"name":"Alice","age":30},{"name":"Bob","age":25},{"name":"Carol","age":35}]	Rows fetched unsorted
3	Sort rows by age ascending	[{"name":"Alice","age":30},{"name":"Bob","age":25},{"name":"Carol","age":35}]	age	[{"name":"Bob","age":25},{"name":"Alice","age":30},{"name":"Carol","age":35}]	Rows sorted by age
4	Return sorted rows	[{"name":"Bob","age":25},{"name":"Alice","age":30},{"name":"Carol","age":35}]	age	[{"name":"Bob","age":25},{"name":"Alice","age":30},{"name":"Carol","age":35}]	Query ends with sorted output

💡 All rows sorted by age ascending, query completes.

Variable Tracker

Variable	Start	After Fetch	After Sort	Final
rows	N/A	[{"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 do the rows change order only after the sorting step?

Because initially rows are fetched in the order stored in the table (see step 2). The ORDER BY clause sorts them afterward (step 3), changing their order before returning.

Does ORDER BY change the data in the table?

No, ORDER BY only changes the order of rows in the query result, not the actual data stored in the table. This is shown by rows before and after sorting in the execution table.

Visual Quiz - 3 Questions

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

A [{"name":"Carol","age":35},{"name":"Alice","age":30},{"name":"Bob","age":25}]

B [{"name":"Alice","age":30},{"name":"Bob","age":25},{"name":"Carol","age":35}]

C [{"name":"Bob","age":25},{"name":"Alice","age":30},{"name":"Carol","age":35}]

D [{"name":"Alice","age":30},{"name":"Carol","age":35},{"name":"Bob","age":25}]

💡 Hint

Check the 'Rows After' column in step 3 of the execution table.

At which step does the query actually return the sorted rows?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Look at the 'Action' column describing when rows are returned.

If the ORDER BY clause was removed, how would the 'Rows After' in step 3 change?

A Rows would be sorted by age ascending

B Rows would remain unsorted as in step 2

C Rows would be sorted by age descending

D Rows would be empty

💡 Hint

Without ORDER BY, rows keep their original order after fetching.

Concept Snapshot

ORDER BY single column syntax:
SELECT columns FROM table ORDER BY column_name [ASC|DESC];
By default, ORDER BY sorts ascending (ASC).
It sorts the query result rows by the specified column.
Does not change table data, only output order.
Useful to organize results for reading or further processing.

Full Transcript

This visual execution shows how an SQL query with ORDER BY on a single column works. First, the query starts and fetches all rows from the table unsorted. Then, it sorts these rows by the specified column in ascending order. Finally, it returns the sorted rows as the query result. The variable 'rows' changes from unsorted to sorted after the sorting step. ORDER BY affects only the output order, not the stored data. This step-by-step trace helps beginners see exactly when and how sorting happens in the query execution.

Practice

1. What does the SQL clause ORDER BY do when used with a single column?

easy

A. It deletes rows that have duplicate values in that column.

B. It filters the rows to only show those with unique values in that column.

C. It groups the rows by the values in that column.

D. It sorts the query results based on the values in that column.

Solution

1. Step 1: Understand the purpose of ORDER BY

   The ORDER BY clause is used to arrange the rows returned by a query in a specific order based on one or more columns.

2. Step 2: Focus on single column sorting

   When used with a single column, it sorts the results either ascending (default) or descending based on that column's values.

3. Final Answer:

   It sorts the query results based on the values in that column. -> Option D

4. Quick Check:

   ORDER BY = Sort results [OK]

Hint: ORDER BY arranges rows by column values [OK]

Common Mistakes:

• Confusing ORDER BY with filtering or grouping
• Thinking ORDER BY removes duplicates
• Assuming ORDER BY changes data in the table

2. Which of the following is the correct syntax to sort the table Employees by the column LastName in ascending order?

easy

A. SELECT * FROM Employees ORDER BY LastName DESC;

B. SELECT * FROM Employees SORT BY LastName;

C. SELECT * FROM Employees ORDER BY LastName ASC;

D. SELECT * FROM Employees ORDER LastName;

Solution

1. Step 1: Recall correct ORDER BY syntax

   The correct syntax to sort results is ORDER BY column_name. To specify ascending order, use ASC (which is also default).

2. Step 2: Check each option

   SELECT * FROM Employees ORDER BY LastName ASC; uses correct syntax with ORDER BY LastName ASC. SELECT * FROM Employees SORT BY LastName; uses invalid keyword SORT BY. SELECT * FROM Employees ORDER LastName; misses BY. SELECT * FROM Employees ORDER BY LastName DESC; sorts descending, not ascending.

3. Final Answer:

   SELECT * FROM Employees ORDER BY LastName ASC; -> Option C

4. Quick Check:

   ORDER BY + column + ASC = correct syntax [OK]

Hint: Use ORDER BY column ASC for ascending sort [OK]

Common Mistakes:

• Using SORT BY instead of ORDER BY
• Omitting BY keyword
• Confusing ASC with DESC

3. Given the table Products with columns ProductID and Price, what will be the order of ProductID after running this query?

SELECT ProductID FROM Products ORDER BY Price DESC;

Assume the data:
ProductID: 1, 2, 3
Price: 10, 30, 20

medium

A. [1, 3, 2]

B. [2, 3, 1]

C. [2, 1, 3]

D. [3, 2, 1]

Solution

1. Step 1: Understand ORDER BY Price DESC

   The query sorts rows by Price in descending order, so highest price first.

2. Step 2: Sort the data by Price descending

   Prices are 10 (ProductID 1), 30 (ProductID 2), 20 (ProductID 3). Descending order: 30, 20, 10. Corresponding ProductIDs: 2, 3, 1.

3. Final Answer:

   [2, 3, 1] -> Option B

4. Quick Check:

   Price DESC sorts highest to lowest [OK]

Hint: Sort by column DESC to get highest values first [OK]

Common Mistakes:

• Mixing ascending and descending order
• Sorting by ProductID instead of Price
• Assuming default ascending order

4. Identify the error in this SQL query:

SELECT Name FROM Customers ORDER LastName;

medium

A. Missing BY keyword after ORDER

B. ORDER BY cannot be used with SELECT

C. Name column does not exist

D. ORDER BY requires two columns

Solution

1. Step 1: Check ORDER BY syntax

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

2. Step 2: Verify other parts

   The SELECT statement is valid, and Name is assumed to exist. ORDER BY can be used with SELECT. Only one column is needed for ORDER BY.

3. Final Answer:

   Missing BY keyword after ORDER -> Option A

4. Quick Check:

   ORDER BY requires BY keyword [OK]

Hint: ORDER BY must include BY keyword [OK]

Common Mistakes:

• Omitting BY after ORDER
• Thinking ORDER BY needs multiple columns
• Assuming ORDER BY can't be used with SELECT

5. You have a table Students with columns StudentID, Name, and Score. You want to list all students ordered by their Score from highest to lowest, but if two students have the same score, order them alphabetically by Name. Which query correctly 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: Analyze each option

   SELECT * FROM Students ORDER BY Score DESC, Name ASC; orders by Score DESC then Name ASC, matching requirements. 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, wrong directions. SELECT * FROM Students ORDER BY Score DESC; only sorts by Score DESC, no tie-breaker.

3. Final Answer:

   SELECT * FROM Students ORDER BY Score DESC, Name ASC; -> Option A

4. Quick Check:

   ORDER BY primary DESC, secondary ASC for tie-break [OK]

Hint: Use ORDER BY primary DESC, secondary ASC for tie-breaks [OK]

Common Mistakes:

• Reversing sort order directions
• Not adding secondary column for tie-break
• Using only one column when two are needed