SQL and MySQL course Β· Module 2: Querying data (SELECT)

LIMIT - limiting the number of rows

4 min read Β·
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:

tytulcena
Elementy89.00
Polityka61.20
Etyka58.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:

tytulrok_wydania
Tom Sawyer2020
Hamlet2020
Pan Tadeusz2015
Dziady2015
Etyka2012

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:

tytulrok_wydania
Hamlet2020
Tom Sawyer2020
Dziady2015
Pan Tadeusz2015
Etyka2012

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:

imienazwisko
LenaBaran
IgorDudek
AdamKaczmarek
JanKowalski
UlaKrawczyk
KasiaLis
OlaMazur
AnnaNowak
AnnaNowak
JanNowak

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. 1. What will the query "SELECT tytul FROM ksiazki LIMIT 5;" return?

  2. 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.

  1. QuestionHow LIMIT works
  2. QuestionPosition of LIMIT in a query
  3. Coding taskFirst five readers
  4. Put in orderRanking of the three most expensive
  5. Coding taskThree most expensive books

Useful articles