Level 4: Sorting, limiting, removing duplicates

Lesson

Without ORDER BY, the order of the result rows is not guaranteed: it may look stable, but it can change from one run or one engine version to another. Whenever the order matters, you write it.

ORDER BY column sorts from smallest to largest: this is ASC, the default sort. For text, it is alphabetical order; for dates in the 'YYYY-MM-DD' format, chronological order. DESC sorts from largest to smallest.

You can sort on several columns: the second one only breaks the ties of the first. ORDER BY department, salary DESC lists the employees by department, then, within each department, from best to least paid. Each column has its own sort direction.

LIMIT n keeps only the first n rows, after sorting. That is how you get a podium: ORDER BY rating DESC LIMIT 3. OFFSET k first skips k rows: LIMIT 1 OFFSET 1 gives the second row. Without ORDER BY, LIMIT keeps arbitrary rows.

SELECT DISTINCT removes duplicate rows from the result. It applies to the whole row: SELECT DISTINCT city, country keeps each different (city, country) pair.

NULL values have no natural place in a sort. SQLite puts them first in an ascending sort and last in a descending sort; PostgreSQL does the opposite. To choose, you write NULLS FIRST or NULLS LAST: ORDER BY director_id NULLS LAST.

Order in which clauses are written: SELECT, FROM, WHERE, ORDER BY, LIMIT. ORDER BY runs after SELECT: unlike WHERE, it can therefore use an alias.

Syntax

SELECT DISTINCT columns
FROM my_table
WHERE …
ORDER BY column DESC
LIMIT 3 OFFSET 0;

Worked example

SELECT title, rating
FROM movies
ORDER BY rating DESC
LIMIT 3;

We sort by descending rating, then keep the first 3 rows: the movie podium.

Example result

Key points

• ASC (ascending) is the default sort, DESC the descending sort.
• Clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT.
• DISTINCT goes right after SELECT.

Common pitfalls

• LIMIT without ORDER BY gives rows “at random”.
• When there are ties, add a second sort column to get a stable order.
• In SQLite, NULLs come first in an ascending sort: filter them out or add NULLS LAST.

The level’s 5 exercises

1. Guided · Show the title and year of the movies, from oldest to newest. (Table: movies)
2. Practice · Show the list of movie genres, without duplicates. (Table: movies)
3. Practice · Show the title and the director id (director_id) of each movie, sorted by increasing id, with the movies without a director at the end. For equal ids, sort by title. (Table: movies)
4. Practice · Show the name, department and salary of the employees, sorted by department (alphabetically), then by descending salary, then by name. (Table: staff)
5. Challenge · Show the airline, destination and price of the second cheapest flight departing from 'CDG'. (Table: flights)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic