Sorting and Limiting: ORDER BY and LIMIT
Put your rows in the right order, then keep just the ones you want
Big Idea: A table stores rows, but it does not promise any order.
ORDER BY lets you sort the rows however you like, and LIMIT lets you keep only the first few. Together they answer questions like "Who are the top 3 players?"
By the end you will be able to:
- sort rows with
ORDER BY, low-to-high (ASC) or high-to-low (DESC), - break ties by sorting on more than one column,
- keep only a handful of rows with
LIMIT, - skip rows with
OFFSETto build pages, - write a "top-N" query to find the best or worst rows.
1. Our example: a game leaderboard
Every example in this lesson uses one small table called players. It is the scoreboard of a video game. Let us build it once so we all picture the same data.
CREATE TABLE players (
id INT,
name VARCHAR(20),
score INT,
level INT,
country VARCHAR(15)
);
INSERT INTO players (id, name, score, level, country) VALUES
(1, 'Aisha', 950, 7, 'Kenya'),
(2, 'Bilal', 720, 5, 'Egypt'),
(3, 'Chen', 950, 8, 'China'),
(4, 'Diego', 610, 4, 'Peru'),
(5, 'Elena', 880, 6, 'Spain'),
(6, 'Farah', 720, 5, 'Iran'),
(7, 'Grace', 1040, 9, 'Kenya'),
(8, 'Hiro', 610, 4, 'Japan');If we just ask for everything, the database is free to hand the rows back in any order (often the order they were stored):
SELECT name, score, level, country FROM players;| name | score | level | country |
|---|---|---|---|
| Aisha | 950 | 7 | Kenya |
| Bilal | 720 | 5 | Egypt |
| Chen | 950 | 8 | China |
| Diego | 610 | 4 | Peru |
| Elena | 880 | 6 | Spain |
| Farah | 720 | 5 | Iran |
| Grace | 1040 | 9 | Kenya |
| Hiro | 610 | 4 | Japan |
This is a messy scoreboard! Nobody wants a leaderboard that is not in order. Let us fix that.