SQL and MySQL course Β· Module 2: Querying data (SELECT)
LIMIT - limiting the number of rows
In this lesson5
Sometimes you do not want the whole shelf - only the first few scrolls. That is what LIMIT is for.
LIMIT - only N rows
Imagine you are given an unknown shelf and just want to glance at what lies on it. Our shelf has seventeen scrolls, but the shelves of real libraries hold thousands of rows. For a preview the first few are enough. We add LIMIT at the very end of the query, followed by the number of rows:
SELECT tytul FROM ksiazki LIMIT 5;The Library counts out five scrolls and stops:
| tytul |
|---|
| Iliada |
| Odyseja |
| PaΕstwo |
| Uczta |
| Antygona |
This returns five books - the first ones in whatever order the Library happens to give them. Without ORDER BY that order is not fixed, so LIMIT almost always goes together with sorting. For a preview such a result is enough, but it proves nothing: these are neither "the five oldest" nor "the five most important" books.
A ranking: ORDER BY and LIMIT
When you combine LIMIT with sorting, true rankings are born. First ORDER BY arranges the scrolls, then LIMIT keeps only the top of the list:
SELECT tytul, cena
FROM ksiazki
ORDER BY cena DESC
LIMIT 3;This query shows the three most expensive books - we sort descending by price and take only the first three scrolls:
| tytul | cena |
|---|---|
| Elementy | 89.00 |
| Polityka | 61.20 |
| Etyka | 58.00 |
The other fourteen books still stand on the shelf. LIMIT removes nothing; it only shortens the answer.
A tie at the boundary
A ranking has one trap. Ask for the five newest books:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania DESC
LIMIT 5;The result looks innocent:
| tytul | rok_wydania |
|---|---|
| Tom Sawyer | 2020 |
| Hamlet | 2020 |
| Pan Tadeusz | 2015 |
| Dziady | 2015 |
| Etyka | 2012 |
Yet two books were published in 2012: "Etyka" and "Fajdros". There is only one fifth place, so MySQL may show either of them. The MySQL 8.0 documentation (chapter "LIMIT Query Optimization") says it plainly: when several rows have equal values in the ORDER BY columns, the server may return them in any order. Today "Etyka" is fifth, tomorrow it may be "Fajdros". A second sort field helps:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania DESC, tytul ASC
LIMIT 5;Now the alphabet decides the fifth place, so the result is always the same:
| tytul | rok_wydania |
|---|---|
| Hamlet | 2020 |
| Tom Sawyer | 2020 |
| Dziady | 2015 |
| Pan Tadeusz | 2015 |
| Etyka | 2012 |
Where does LIMIT stand?
Remember the sacred order: SELECT β FROM β ORDER BY β LIMIT. LIMIT always goes at the very end of the query, after the sorting. Here are ten readers in alphabetical order:
SELECT imie, nazwisko
FROM czytelnicy
ORDER BY nazwisko ASC
LIMIT 10;First the sorting by surname, then LIMIT keeps the first ten rows:
| imie | nazwisko |
|---|---|
| Lena | Baran |
| Igor | Dudek |
| Adam | Kaczmarek |
| Jan | Kowalski |
| Ula | Krawczyk |
| Kasia | Lis |
| Ola | Mazur |
| Anna | Nowak |
| Anna | Nowak |
| Jan | Nowak |
All three people named Nowak fit into the top ten, so here the tie does not change who is on the list - at most their order among themselves.
LIMIT also has two edge cases. If you ask for more rows than the shelf holds, e.g. LIMIT 100, you simply get all of them. And LIMIT 0 returns an empty result, without a single scroll.
Why limit the result?
- Preview - you want just a glance at the first rows, not the whole shelf.
- Rankings - "the top three", "the five newest" are born from ORDER BY + LIMIT.
- Speed - the Library need not return thousands of scrolls at once.
My advice: use LIMIT without ORDER BY only for a preview. When the result has to mean something - a ranking, a price list, a list of prizes - fix the order first, and when ties are possible in the sort field, add a second one.
LIMIT is the scribe's hand that stops the stream of scrolls once the requested number is counted. In the next lesson we will add OFFSET to it and learn to paginate.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What will the query "SELECT tytul FROM ksiazki LIMIT 5;" return?
2. Where in a query do we place the LIMIT clause?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the czytelnicy table and shows only the first 5 rows.
- Vertical ordering
Arrange the clauses of a ranking of the three most expensive books in the correct order (top to bottom):
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table, sorts in descending order by price and shows only the 3 most expensive books.