SQL Limit

LIMIT is the part of a query that caps how many rows a result can hold. The query runs as usual, and the answer stops at the cap.

A plain cap

This query sorts the customers by id and caps the answer at three rows. The first three customers come back and the rest stay out.

SELECT name FROM customers ORDER BY customer_id LIMIT 3;

Top rows of a sort

ORDER BY sorts the products by price first, dearest at the top. LIMIT then keeps the top three rows of that sort.

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

The next page

OFFSET skips the first three rows of the sort. LIMIT then takes the next three, like a second page of results.

SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 3;

Example

SELECT name, stock FROM products ORDER BY stock DESC LIMIT 2;

Key points

LIMIT: the keyword that caps the rows in a result
LIMIT comes last in a query, after ORDER BY.
ORDER BY with LIMIT picks the top rows of a sort.
OFFSET: the keyword that skips rows before the cap starts
A cap above the row count changes nothing.

In this example, ORDER BY sorts the products by stock, largest first. LIMIT keeps the top two rows of that sort. The answer names the two products with the most stock.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

SELECT name, stock FROM products ORDER BY stock DESC LIMIT 2;

Exercise

Which query returns the two dearest products?

SELECT name FROM products ORDER BY price DESC LIMIT 2; SELECT name FROM products LIMIT 2; SELECT name FROM products ORDER BY price LIMIT 2; SELECT name FROM products WHERE price LIMIT 2;