Learn SQL: ORDER BY
Sorting with ORDER BY
Noor slides a laptop towards you. "The sponsors want to know who the big names are. And the poster has to be finished. Can't that list just be in order?" It can.
ORDER BY puts rows in order
Without ORDER BY a database promises no order at all: the rows come out however it likes. With ORDER BY you choose the column to sort on.
By default SQL sorts ascending: from small to large, from A to Z, from early to late. Here the cheapest artist is at the top.
SELECT name, fee FROM artists ORDER BY fee| name | fee |
|---|---|
| Bas & Bram | 2000 |
| aurora borealis | 2500 |
| Zonnebloem Collectief | 3000 |
| Vuurvogel | 3500 |
| Polderblues | 3800 |
| Jazzkwartet Mol | 4000 |
| Marieke Staal | 4500 |
| Echo Echo | 5000 |
DESC flips the order
Put DESC (descending) after the column and the largest comes first. Its opposite, ASC, is the default: you may leave it out.
Add LIMIT and you get a top list: the three artists with the most followers. The order of the pieces is fixed: SELECT … FROM … WHERE … ORDER BY … LIMIT.
SELECT name, followers FROM artists ORDER BY followers DESC LIMIT 3| name | followers |
|---|---|
| Kiki Moreau | 1250000 |
| The Velvet Owls | 820000 |
| Neon Atlas | 690000 |
Where does NULL go?
For three artists Noor doesn't know the number of followers yet: they have NULL. To SQLite, NULL is smaller than everything, so they come first when sorting ascending and last with DESC.
Don't want them in your list? Filter them out with WHERE followers IS NOT NULL, as you learned at the video shop.
SELECT name, followers FROM artists ORDER BY followers| name | followers |
|---|---|
| aurora borealis | NULL |
| Vuurvogel | NULL |
| Bas & Bram | NULL |
| Zonnebloem Collectief | 12000 |
| Polderblues | 15000 |
| Jazzkwartet Mol | 21000 |
| Marieke Staal | 39000 |
| Echo Echo | 45000 |
Try it yourself
Who are the smallest names? Show name and followers of the five artists with the fewest followers, leaving out the artists whose number is unknown.
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson