SQL Foundations Lesson 5 of 7

SQL ORDER BY and LIMIT

25 min, 5 exercisesAQA 3.7.2

Builds on lessons 1 to 4. Start at lesson 1

Learn

Sorting with ORDER BY

Here is something that surprises people: without instructions, a database does not promise any order. Rows often come back in the order they were added, but that is luck, not a rule. If order matters, say so with ORDER BY:

SELECT gamertag, score FROM leaderboard ORDER BY score;

That sorts from smallest to biggest, which is called ascending (ASC, the default). Leaderboards want the biggest first, so add DESC for descending:

SELECT gamertag, score FROM leaderboard ORDER BY score DESC;

Learn

Ties and LIMIT

Text sorts alphabetically, and dates stored as '2026-03-14' sort correctly too, which is exactly why that format is popular.

What about ties? You can list more than one column. The second one only matters when the first one is equal:

ORDER BY level DESC, gamertag ASC

means "highest level first, and players on the same level in alphabetical order".

To keep only the first few rows, add LIMIT at the very end:

SELECT gamertag, score FROM leaderboard ORDER BY score DESC LIMIT 3;

Learn

The full query order

That is your podium. LIMIT without ORDER BY is legal but kind of pointless: you get "some 3 rows", and which ones is up to the database's mood.

The full order of the parts so far is:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

The database filters first (WHERE), then sorts what is left, then chops it with LIMIT. So WHERE region = 'EU' ORDER BY score DESC LIMIT 1 gives the best EU player, not the best player overall who happens to be from the EU.

GCSE exam boards
  • AQA 3.7.2 SQL

GCSE Python path The GCSE lesson

Try

Run it: the top three

The query in the editor runs against this lesson's tables (open Tables above the editor to see them). Run it, change the WHERE or the columns, run it again.

Tables leaderboard
leaderboard 8 rows, 5 columns
idgamertagregionlevelscore
1NoScopeNanEU4218200
2xX_Toast_XxNA3715900
3LagIsMyFaultOCE4221050
4QuietKeyboardEU299400
5BrbSnacksNA3716800
6MumSaysBedtimeEU122300
7ZeroDeathsOCE5025500
8AFKAlexNA298800
Show the SQL that built them
CREATE TABLE leaderboard (id INTEGER PRIMARY KEY, gamertag TEXT, region TEXT, level INTEGER, score INTEGER);
INSERT INTO leaderboard VALUES
 (1, 'NoScopeNan', 'EU', 42, 18200),
 (2, 'xX_Toast_Xx', 'NA', 37, 15900),
 (3, 'LagIsMyFault', 'OCE', 42, 21050),
 (4, 'QuietKeyboard', 'EU', 29, 9400),
 (5, 'BrbSnacks', 'NA', 37, 16800),
 (6, 'MumSaysBedtime', 'EU', 12, 2300),
 (7, 'ZeroDeaths', 'OCE', 50, 25500),
 (8, 'AFKAlex', 'NA', 29, 8800);
example.sql to run
-- The podium: top 3 scores
SELECT gamertag, score
FROM leaderboard
ORDER BY score DESC
LIMIT 3;

Practice

Level ranking

Return gamertag and level for every player, sorted by level from highest to lowest. Players on the same level should be in alphabetical order of gamertag.

exercise_1.sql to run
SELECT gamertag, level
FROM leaderboard
ORDER BY level;

Practice

Best in NA

Return the gamertag and score of the two highest scoring players in the NA region, highest first.

exercise_2.sql to run
SELECT gamertag, score
FROM leaderboard
ORDER BY score DESC
LIMIT 2;

Practice

Page two of the leaderboard

The leaderboard shows three players per page. Return the gamertag and score of the players in 4th, 5th and 6th place.

LIMIT 3 OFFSET 3 skips the first three rows, then takes three.

exercise_3.sql to run
SELECT gamertag, score
FROM leaderboard
ORDER BY score DESC
LIMIT 3;

PracticePredict the output

Lowest two of levels 29 and 37

What does this query return? One gamertag per line.

exercise_4.sql
SELECT gamertag
FROM leaderboard
WHERE level = 37 OR level = 29
ORDER BY score
LIMIT 2;

Your turn

Most points per level

Who scores the most for their level? Return each player's gamertag and per_level: their score divided by their level, as a decimal. Keep the best three, highest first.

On your own. Hints open after your first check.

exercise_5.sql to run
-- Your query here
SQLite