Learn/SQL/3. Sorting and limiting
Lesson 3 of 8
Sign in

SQL, lesson 3

Sorting and limiting

A table has no built-in order. SQLite often hands rows back in the order they were inserted, but nothing promises that, and it can change once a table grows or gets an index. If order matters, say so with ORDER BY.

Ascending and descending

ORDER BY sorts from smallest to largest by default (ASC). Add DESC to reverse it. Numbers sort by value, text sorts alphabetically (capital letters before lowercase ones).

Breaking ties

List more than one sort key, separated by commas. The second key is only used when two rows tie on the first, and so on. Each key gets its own direction.

Adding a tie-breaker is a good habit: it makes your output the same every time you run it.

You can also sort by an alias or an expression: ORDER BY length(player) sorts by name length.

2 exercises to try what you just read.

Exercise 1

The podium

Press Ctrl Enter or Check to test your code.

Exercise 2

Where did we travel?

Press Ctrl Enter or Check to test your code.

Where do NULL values go? SQLite treats NULL as smaller than everything, so it comes first in ascending order and last in descending order. Add NULLS LAST or NULLS FIRST to choose.

Keeping only a few rows

LIMIT n stops after n rows. Combined with ORDER BY, it answers "top three" questions. OFFSET k skips the first k rows first, which is how pages of results work.

The clauses must appear in this order: SELECT … FROM … WHERE … ORDER BY … LIMIT … OFFSET ….

Removing duplicates

SELECT DISTINCT drops repeated rows from the result. It looks at all the selected columns together, so DISTINCT city, country keeps each pair once.

Further reading

  • SQLite: SELECT (ORDER BY, LIMIT, DISTINCT)

The table scores has the columns player and score:

Print the player and score of the top three players: highest score first. When two players have the same score, list them in alphabetical order of name. Expected output:

The table trips records holidays, with the columns city and year. Some cities were visited more than once:

Print each city once, in alphabetical order. Expected output:

SELECT player, score FROM scores ORDER BY score;
SELECT player, score FROM scores ORDER BY score DESC;
-- Highest score first; equal scores in alphabetical order of name
SELECT player, score FROM scores ORDER BY score DESC, player ASC;
SELECT player, score FROM scores ORDER BY score DESC LIMIT 3;          -- podium
SELECT player, score FROM scores ORDER BY score DESC LIMIT 3 OFFSET 3; -- places 4 to 6
SELECT DISTINCT team FROM scores ORDER BY team;
player  | score
Maya    | 820
Priya   | 940
Sam     | 610
Leo     | 940
Noah    | 875
Zoe     | 700
Leo|940
Priya|940
Noah|875
city      | year
Lisbon    | 2022
Oslo      | 2023
Lisbon    | 2024
Kyoto     | 2023
Oslo      | 2025
Accra     | 2024
Accra
Kyoto
Lisbon
Oslo