CASE in ORDER BY in SQL - Step-by-Step Execution

Concept Flow - CASE in ORDER BY

Start Query Execution

↓

Evaluate CASE in ORDER BY

↓

Assign Sort Value per Row

↓

Sort Rows by Assigned Values

↓

Return Sorted Result Set

↓

End

The database evaluates the CASE expression for each row in ORDER BY, assigns a sort value, then sorts rows accordingly before returning the result.

Execution Sample

SQL

SELECT name, score
FROM students
ORDER BY CASE
  WHEN score >= 90 THEN 1
  WHEN score >= 75 THEN 2
  ELSE 3
END;

This query sorts students by score groups: highest scores first, then medium, then others.

Execution Table

Step	Row (name, score)	CASE Evaluation	Sort Value	Action
1	(Alice, 92)	score >= 90 is TRUE	1	Assign sort value 1
2	(Bob, 85)	score >= 90 is FALSE; score >= 75 is TRUE	2	Assign sort value 2
3	(Charlie, 70)	score >= 90 is FALSE; score >= 75 is FALSE	3	Assign sort value 3
4	(Diana, 95)	score >= 90 is TRUE	1	Assign sort value 1
5	(Evan, 60)	score >= 90 is FALSE; score >= 75 is FALSE	3	Assign sort value 3
6	Sort all rows by sort value ascending			Rows ordered: Alice, Diana, Bob, Charlie, Evan
7	Return sorted result set			Query ends

💡 All rows assigned sort values; sorting complete; query returns ordered rows.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
sort_value (per row)	N/A	1 (Alice)	2 (Bob)	3 (Charlie)	1 (Diana)	3 (Evan)	All assigned

Key Moments - 2 Insights

Why does the CASE expression assign numbers like 1, 2, 3 in ORDER BY?

The CASE assigns numeric values to group rows for sorting priority. Lower numbers sort first. See execution_table rows 1-5 where each row gets a sort value.

What happens if multiple rows get the same sort value?

Rows with the same sort value keep their relative order or can be further sorted by other criteria. In the example, Alice and Diana both get 1 and appear first.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what sort value is assigned to Charlie?

A 1

B 2

C 3

D 0

💡 Hint

Check the 'Sort Value' column for step 3 in execution_table.

At which step does the query start sorting all rows by their assigned sort values?

A Step 6

B Step 4

C Step 2

D Step 7

💡 Hint

Look for the step describing sorting action in execution_table.

If we changed the CASE to assign 3 for score >= 90 and 1 for others, which rows would appear first?

A Alice and Diana

B Charlie and Evan

C Bob only

D No change

💡 Hint

Refer to how sort values determine order in variable_tracker and execution_table.

Concept Snapshot

CASE in ORDER BY lets you sort rows by custom conditions.
Syntax: ORDER BY CASE WHEN condition THEN value ELSE value END
Rows get sort values from CASE.
Lower values sort first.
Useful for grouping or custom priority sorting.

Full Transcript

This visual execution shows how SQL uses CASE in ORDER BY to sort rows. Each row is checked against CASE conditions, which assign a numeric sort value. Rows are then sorted by these values, with lower numbers appearing first. For example, students with scores 90 or above get sort value 1, so they appear first. This method helps group and order data flexibly. The execution table traces each row's CASE evaluation and assigned sort value, then the sorting step. Variable tracking shows how sort values change per row. Key moments clarify why numeric values are used and what happens with ties. The quiz tests understanding of sort values and sorting steps. The snapshot summarizes how CASE in ORDER BY works for custom sorting.