SQL: ORDER BY — AQA GCSE CS (8525)

Spec & Goals 3 min

AQA Spec 3.7.2 — Sorting results with ORDER BY (ASC / DESC)

By the end of this lesson you can:

Use ORDER BY to sort query results.
Sort ascending (ASC) and descending (DESC).
Combine WHERE and ORDER BY in one query.

Warm-Up 5 min

You can now choose columns (SELECT), filter rows (WHERE) and match patterns (LIKE). The last retrieval clause puts results in order.

Quick starter

To show the class ranked from highest mark to lowest, do you want ascending or descending order?

Reveal the idea

Descending — highest first. In SQL: ORDER BY Mark DESC.

Key Concept — sorting results 14 min

SELECT fields
FROM table
WHERE condition
ORDER BY field ASC;   -- or DESC

Keyword	Order	Notes
`ORDER BY field`	Ascending by default	A→Z, smallest→largest.
`ASC`	Ascending (explicit)	Same as the default.
`DESC`	Descending	Z→A, largest→smallest.

ORDER BY — the SQL clause that sorts the results by a field, ascending (ASC) or descending (DESC).

Clause order matters

The clauses must appear in this order in the statement:

SELECT ... FROM ... WHERE ... ORDER BY ...;

Key point: ORDER BY always comes last, after any WHERE. Default is ascending; add DESC for descending. You can sort by a field you didn't display, but it's clearer to include it.

Exam tip: writing ORDER BY before WHERE is a common mistake that loses the structure mark. Memorise: SELECT → FROM → WHERE → ORDER BY.

Worked Example — sort the students 12 min

Query 1: all students, highest mark first.

SELECT Name, Mark
FROM Student
ORDER BY Mark DESC;

Name	Mark
Priya	91
Mei Ling	88
Aisyah	78
Hafiz	72
Wei Jie	65
Arjun	54

Query 2: students from Penang, sorted by name A→Z.

SELECT Name
FROM Student
WHERE Town = 'Penang'
ORDER BY Name ASC;

Name
Mei Ling
Wei Jie

How the marks are earned: correct field after ORDER BY (1) and the right direction ASC/DESC (1). Putting ORDER BY last, after WHERE, keeps the structure mark.

Try It Yourself 12 min

🟢 Easy

Goal: Write a query listing all student names in alphabetical order.

🟡 Medium

Goal: Write a query showing Name and Mark of all students, lowest mark first.

🔴 Stretch

Goal: Write a query for students in year 10 forms (LIKE '10_'), sorted by mark highest first.

📝 Exam Practice 10 min

State[1 mark]

State what ORDER BY Mark DESC does.

Mark scheme

Sorts the results by Mark in descending order (highest first) (1).

Write a program[3 marks]

Write a query to display the Name and Mark of every student, sorted from highest mark to lowest.

Mark scheme

SELECT Name, Mark (1).
FROM Student (1).
ORDER BY Mark DESC; (1).

Write a program[2 marks]

Write a query listing the names of students from Ipoh, in alphabetical order.

Mark scheme

SELECT Name FROM Student WHERE Town = 'Ipoh' (1).
ORDER BY Name ASC; (accept without ASC) (1).

Recap & Key Terms 3 min

ORDER BY field sorts results — ascending by default, or DESC for descending. It always comes last, after any WHERE. The full retrieval order is SELECT → FROM → WHERE → ORDER BY.

ORDER BY: Sorts query results by a chosen field.
ASC: Ascending order (A→Z, low→high) — the default.
DESC: Descending order (Z→A, high→low).

Homework 1 min

Task (≤ 15 min): For a Product(ItemName, Price, Category) table, write queries: (a) all products sorted by price, cheapest first; (b) products in the 'Drinks' category sorted by name; (c) all products, most expensive first.

Model answer

-- (a)
SELECT * FROM Product ORDER BY Price ASC;

-- (b)
SELECT ItemName FROM Product
WHERE Category = 'Drinks' ORDER BY ItemName;

-- (c)
SELECT ItemName, Price FROM Product ORDER BY Price DESC;

Award marks for: correct ASC sort (1); WHERE + ORDER BY together (1); correct DESC sort (1).