Sorting, DISTINCT, LIMIT

Controlling the order and size of a result: ORDER BY on one or several keys, DISTINCT on the combination of displayed columns, LIMIT and OFFSET.

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

ORDER BY: what is it?
Show the answer
The clause that sorts the rows of the result by one or more columns, from the most to the least important.
Without it, the database returns rows in whatever order suits it, which may change from one run to the next: no order is guaranteed.
Which rows does this query return?

```sql
-- film : titre, annee
-- Cendres 2021 | Bal masqué 2019 | Les Voisins 2015 | Orbite 2012
-- Horizon 2010 | Zéro absolu 2004 | Nuit blanche 1998 | Terminus 1985
SELECT titre FROM film
ORDER BY annee DESC
LIMIT 3 OFFSET 2;
`
- Les Voisins, Orbite, Horizon
- Cendres, Bal masqué, Les Voisins
- Bal masqué, Les Voisins, Orbite
- Orbite, Horizon, Zéro absolu
Show the answer
Les Voisins, Orbite, Horizon
The descending sort gives Cendres, Bal masqué, Les Voisins, Orbite, Horizon… OFFSET 2 skips the first two, LIMIT 3 keeps the next three.
In ORDER BY ville, age DESC, rows are sorted first by …, then, for equal ville, by … descending.
Show the answer
In ORDER BY ville, age DESC, rows are sorted first by ville, then, for equal ville, by age descending.
The first column dominates; the next one only breaks ties left by the previous one.
DESC: what is it?
Show the answer
The keyword that reverses the sort direction for the one column it follows: from largest to smallest.
The default direction is ascending (ASC). In ORDER BY ville, age DESC, only age is descending; DESC must be repeated on each column concerned.