Limiting with LIMIT

A query can easily return thousands (or millions) of rows. When you only want a preview — or when you want to build a top-N ranking — you use the LIMIT clause:

SELECT <colonne>
FROM   <tabella>
[WHERE  <condizione>]
[ORDER BY <colonna> [ASC|DESC]]
LIMIT  <numero>;

LIMIT goes at the end of the query and truncates the result to the given number of rows. On its own it is not enough to build a ranking: without ORDER BY, SQL does not guarantee which rows you get back. So the golden rule is:

Top-N = ORDER BY + LIMIT.

Examples

-- Le prime 5 righe della tabella, in ordine arbitrario:
SELECT * FROM employees LIMIT 5;

-- I 3 dipendenti più pagati:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- I 5 progetti con il budget più basso:
SELECT name, budget
FROM projects
ORDER BY budget ASC
LIMIT 5;

Try it

Review exercise