← Course home N5 Computing Science  ·  James Gillespie's
DDD  ·  Database Design & Development

SQL: Sorting and Ranges

Lesson DDD7 of 10 Approx 60 min Films database required
Learning intentions
  • Use ORDER BY to sort SQL query results into a predictable order.
  • Choose ASC or DESC correctly for text and number fields.
  • Use two-field sorting and range conditions with >= and <=.
Success criteria
  • I can write ORDER BY field ASC and ORDER BY field DESC.
  • I can explain which field is the primary sort and which field is the secondary sort.
  • I can write a range condition using >= low AND <= high.
  • [Extension] I can use BETWEEN low AND high as a shortcut for the same range (not required for N5 exam).
📊
Films database required for this lesson

Use the same films.sql database from DDD1. Open it in DataGrip before you start. If the database is missing, download the script and run it again.

↓ Download films.sql
Warm up — what do you already know?

Answer before the lesson begins. These check prior SQL knowledge from DDD5 and DDD6 — it's fine if you're unsure.

1. Which clause chooses the table a query reads from?

2. Which condition finds films with a rating of 8.0 or higher?

3. What does AND mean in a WHERE clause?

Key vocabulary

ORDER BY
SQL clause that sorts the result of a query. It is written after FROM and after any WHERE clause.
ASC
Ascending order: smallest to largest for numbers, oldest to newest for years, and A to Z for text.
DESC
Descending order: largest to smallest for numbers, newest to oldest for years, and Z to A for text.
Primary sort
The first field listed after ORDER BY. SQL sorts by this field before considering any others.
Secondary sort
A second sort field, written after a comma. It breaks ties when records have the same primary sort value.
BETWEEN [Extension]
A shortcut range operator: BETWEEN 2000 AND 2009 means the same as >= 2000 AND <= 2009. Both boundary values are included. Not required for the N5 exam — use >= / <= instead.

Sorting and ranges in SQL

Why sorting matters

A database table stores records, but the order you see on screen is not something you should rely on. It might look like records are in primary-key order, or in the order they were inserted, but SQL does not guarantee that unless you ask for it. When a question says "alphabetical order", "highest first", "oldest to newest", or "sort by", you need an ORDER BY clause.

ORDER BY is written at the end of a SELECT query. If the query has a WHERE clause, sorting happens after filtering: the database first finds the matching records, then arranges those matching records into the requested order.

SELECT title, year, rating
FROM film
ORDER BY rating DESC;

This query displays every film, sorted by the rating field from highest to lowest. The keyword DESC means descending. For a number field, descending means largest value first. For a year field, descending means newest year first. For a text field, descending means reverse alphabetical order, from Z to A.

ASC and DESC

ASC means ascending. For numbers it means smallest to largest, for years it usually means oldest to newest, and for text it means A to Z. SQL uses ascending order by default if you do not write ASC or DESC, but in an exam answer it is safer to write the direction explicitly. It shows the marker that you understood the requested order.

SELECT title, year
FROM film
ORDER BY title ASC;

That query sorts film titles alphabetically. Be careful not to confuse sorting with filtering. ORDER BY does not remove any records. If the table has 52 films and there is no WHERE clause, the result still has 52 films — just arranged in a different order.

ORDER BY rating DESC — same records, different order Without ORDER BY title rating Pulp Fiction 8.9 The Shawshank Redemption 9.3 Batman Begins 8.2 The Dark Knight 9.0 Schindler's List 8.9 Order is unpredictable — do not rely on it ORDER BY rating DESC With ORDER BY rating DESC title rating The Shawshank Redemption 9.3 The Dark Knight 9.0 Pulp Fiction 8.9 Schindler's List 8.9 Batman Begins 8.2 Highest rating first — 52 records returned, just reordered

Two-field sorting

Sometimes one sort field is not enough. If several records share the same value, SQL needs a tiebreaker. You can sort by more than one field by separating the fields with a comma. The first field is the primary sort; the second field is the secondary sort.

SELECT title, genre, rating
FROM film
ORDER BY genre ASC, rating DESC;

This sorts the films by genre first, so all Action films appear together, all Adventure films appear together, and so on alphabetically. Within each genre group, rating DESC puts the highest-rated films first. The secondary sort does not sort the whole result by rating; it only breaks ties inside each genre group. This is one of the most common SQL misunderstandings at National 5.

ORDER BY genre ASC, rating DESC — two-level sort title genre ↑ (primary) rating ↓ (secondary) The Dark Knight Action 9.0 Batman Begins Action 8.2 The Godfather Crime 9.2 Pulp Fiction Crime 8.9 The Shawshank Redemption Drama 9.3 Schindler's List Drama 8.9 Genres A–Z: Action, Crime, Drama. Within Crime, ratings go 9.2 then 8.9 — highest first.

Range conditions with >= and <=

To filter records within an inclusive range, use >= (greater than or equal to) and <= (less than or equal to) joined with AND. For example, to find films from the 2000s including both boundary years:

SELECT title, year
FROM film
WHERE year >= 2000 AND year <= 2009
ORDER BY year ASC;

Both 2000 and 2009 are included because of the = signs. This is the recommended form for N5 — it uses operators you already know from filtering lessons and is guaranteed to appear in the mark scheme.

year >= 2000 AND year <= 2009 — both endpoints included 1990 2000 2009 2015 2020 included (>= 2000) included (<= 2009) Films from 2000–2009 excluded excluded
Extension — BETWEEN (not required for N5 exam)

BETWEEN is a shorthand for the same range condition. The query above could also be written as:

WHERE year BETWEEN 2000 AND 2009

BETWEEN does not appear in the N5 appendix or in any past paper. If you use it in the exam, your answer may still be accepted — but to be safe, use >= AND <= instead. If you see BETWEEN in real-world SQL (e.g. in DataGrip), you now know what it means.

Combining filtering, ranges, and sorting

Real queries often use more than one idea. You might filter first, use a range, then sort the remaining records. The order of the SQL clauses stays consistent: SELECT, FROM, WHERE, then ORDER BY.

SELECT title, genre, year, rating
FROM film
WHERE genre = "Sci-Fi"
AND rating BETWEEN 8.0 AND 9.0
ORDER BY rating DESC, title ASC;

Read this in English as: show selected fields from the film table, keep only Sci-Fi films with ratings from 8.0 to 9.0 inclusive, then sort by rating highest first and use title alphabetically if two films have the same rating.

Worked examples

Example 1 — highest rated films first

Task: Display the title, year, and rating for every film, sorted from highest rating to lowest.

1
Choose the fields to display: title, year, and rating.
2
Use FROM film. There is no WHERE clause because every film should appear.
3
Add ORDER BY rating DESC, because highest to lowest is descending order.
SELECT title, year, rating
FROM film
ORDER BY rating DESC;

Verified result: the first three rows are The Dark Knight (9.0), Schindler's List (8.9), and Pulp Fiction (8.9). The two 8.9 films may appear in database order because no secondary sort was requested.

Example 2 — two-field sorting

Task: Display each film's title, genre, and year. Sort by genre A to Z. Within each genre, sort newest films first.

1
The primary sort is genre ASC, because genre grouping is mentioned first.
2
The secondary sort is year DESC, because "newest first" means the largest year first inside each genre.
3
Write both sort fields after ORDER BY, separated by a comma.
SELECT title, genre, year
FROM film
ORDER BY genre ASC, year DESC;

Verified result: the Action group starts first. Within Action, the first three films are The Dark Knight (2008), Batman Begins (2005), and Kill Bill: Vol. 1 (2003).

Example 3 — Range condition with year

Task: Find films released between 2000 and 2009, including both 2000 and 2009. Show title and year, oldest first.

1
The range is on the year field — use >= and <= so both boundary values are included.
2
Write WHERE year >= 2000 AND year <= 2009.
3
Use ORDER BY year ASC for oldest to newest.
SELECT title, year
FROM film
WHERE year >= 2000 AND year <= 2009
ORDER BY year ASC;

Verified result: 16 records are returned. The first records are Memento and Gladiator from 2000, proving that the lower boundary is included. [Extension: this could also be written as WHERE year BETWEEN 2000 AND 2009 — identical result, but use >= / <= in the N5 exam.]

Example 4 — filter, range, and sort together

Task: Find Sci-Fi films with a rating between 8.0 and 9.0 inclusive. Sort by rating highest first, then title A to Z.

1
Filter to one genre: genre = "Sci-Fi". Text values need quotation marks.
2
Add the rating range: AND rating >= 8.0 AND rating <= 9.0. Both boundary values are included.
3
Sort primarily by rating descending, then by title ascending as the tiebreaker.
SELECT title, year, rating
FROM film
WHERE genre = "Sci-Fi"
AND rating >= 8.0 AND rating <= 9.0
ORDER BY rating DESC, title ASC;

Verified result: 7 records are returned, starting with Inception (8.8), then Interstellar (8.6), then Alien (8.4).

Now you try

A teacher wants a list of Fantasy films from the films database. They want only films released between 2000 and 2010 inclusive. The list should show the highest-rated film first. If two films have the same rating, the titles should be alphabetical.

Answer the following:

  1. Which table and fields are needed?
  2. Which WHERE conditions are needed?
  3. Write the complete SQL query.
  1. Use the film table. Display title, year, and rating.
  2. Use genre = "Fantasy" and year >= 2000 AND year <= 2010.
  3. SELECT title, year, rating
    FROM film
    WHERE genre = "Fantasy"
    AND year >= 2000 AND year <= 2010
    ORDER BY rating DESC, title ASC;

Verified result: 6 records are returned. The first three are The Lord of the Rings: The Return of the King, The Lord of the Rings: The Fellowship of the Ring, and The Lord of the Rings: The Two Towers.

Common mistakes
✕
Putting ORDER BY before WHERE. The correct order is SELECT, FROM, WHERE, then ORDER BY. Sorting comes after filtering.
✕
Forgetting the = in range conditions. year > 2000 excludes 2000 itself — only films from 2001 onwards appear. Use year >= 2000 to include the boundary value. The same applies to <= on the upper end.
✕
Swapping the primary and secondary sort fields. ORDER BY genre ASC, rating DESC sorts by genre first. It does not sort the whole result by rating first.
✕
Quoting number values unnecessarily. Text values such as "Fantasy" need quotes. Number values such as 2000, 150, and 8.0 do not.
Exam tip

In SQL questions, underline order words in the scenario before writing your answer. "alphabetical" usually means ASC, "highest first" means DESC, "newest first" means year DESC, and "between X and Y inclusive" means >= X AND <= Y — use this form in the exam. Put ORDER BY at the end of the query after WHERE.

Task Set

Questions 1–5 are auto-checked. Questions 6–7 are self-marked SQL writing questions. Questions 8–9 are practical DataGrip tasks using the films database.

1. Which clause sorts query results? TYPE 1

2. What does ORDER BY year DESC do? TYPE 1

3. Which condition finds films released from 1990 to 1999 inclusive? TYPE 1

4. In ORDER BY genre ASC, rating DESC, what is the secondary sort? TYPE 1

5. Which full query displays all film titles alphabetically? TYPE 1

6. Write a SQL query to display title, runtime_mins, and rating for films longer than 150 minutes, sorted from longest to shortest. TYPE 2

SELECT title, runtime_mins, rating
FROM film
WHERE runtime_mins > 150
ORDER BY runtime_mins DESC;

Verified result: 15 records are returned. The first three runtimes are 201, 195, and 195 minutes.

7. Write a SQL query to display title, year, and rating for films released between 2015 and 2019 inclusive. Sort newest films first. TYPE 2

SELECT title, year, rating
FROM film
WHERE year >= 2015 AND year <= 2019
ORDER BY year DESC;

Verified result: 13 records are returned. The first three are 2019 films: Little Women, Parasite, and Us.

8. Practical: In DataGrip, run a query that displays title, genre, and rating for all films sorted by genre A to Z, then rating highest to lowest within each genre. Paste your SQL and record the first three Action films shown. TYPE 3

SELECT title, genre, rating
FROM film
ORDER BY genre ASC, rating DESC;

Verified result: the first three Action films are The Dark Knight (9.0), Gladiator (8.5), and Terminator 2: Judgment Day (8.5). The two 8.5 Action films keep their database order because no title tiebreaker was requested.

9. Practical: In DataGrip, write and run a query to find Fantasy films with a rating of at least 7.5 and no more than 9.0. Display title, year, and rating. Sort by rating highest first, then title A to Z. Paste your SQL and record how many records are returned. TYPE 3

SELECT title, year, rating
FROM film
WHERE genre = "Fantasy"
AND rating >= 7.5 AND rating <= 9.0
ORDER BY rating DESC, title ASC;

Verified result: 7 records are returned.

Teacher notes — Shift+T to hide

Suggested timing: 60 minutes. Warm up + vocabulary 10 min; notes and live DataGrip demo 20 min; worked examples 10 min; now you try 5 min; task set/practical work 15 min.

Practical setup: Pupils need Laragon running, DataGrip open, and the films database selected. Use a query console connected to the films database.

Key misconception to address: The secondary sort is only a tiebreaker inside matching primary-sort groups. Demonstrate ORDER BY genre ASC, rating DESC and then swap the fields to show how different the result becomes.

Live demo suggestion: Start with SELECT title, rating FROM film;, then add ORDER BY rating DESC. Next add , title ASC and point out how ties are handled. Finish with a WHERE year >= 2000 AND year <= 2009 query and explicitly check that both 2000 and 2009 appear in the results.

Note on BETWEEN: BETWEEN is marked as Extension throughout this lesson. It does not appear in the N5 spec appendix or in any past paper. Teach >= AND <= as the core form. If pupils ask about BETWEEN, explain it is a shorthand that means the same thing but that using it in the exam carries a small risk if mark schemes aren't flexible. Extension question: Ask pupils to write the BETWEEN-equivalent form using >= and <=, then ask what would change if the task said "after 2000 but before 2009" (answer: > 2000 AND < 2009, no = signs).

SQA command words covered: "write", "identify", "describe", "explain". Emphasise that "write a query" requires the full SQL statement in the correct clause order.