The First Rows with LIMIT

Often you don't want all the rows, just the first few: the three newest orders, the five cheapest games. LIMIT, at the very end of the query, says how many rows to show at most:

SELECT id, ordered_at
FROM orders
ORDER BY ordered_at DESC
LIMIT 3;

ORDER BY sorts the rows and LIMIT keeps the first three. Together, they answer questions about “the top N”: the most expensive, the newest, the best rated. If there are fewer rows than the limit, you simply get all of them.

LIMIT without ORDER BY is a lottery. SELECT title FROM games LIMIT 3; shows three games, but which three isn't guaranteed: without ORDER BY, the rows come in any order, as you know. That's fine when you just want to take a quick look at a big table, but never when the question is about the first, the best or the last.

Watch out for ties at the edge. The three cheapest games? Cube Logic for $5, Word Quest for $10, and then three games for $15. With ORDER BY price LIMIT 3, which of them gets the third place is up to the database. A second sort column decides it:

SELECT title, price
FROM games
ORDER BY price, title
LIMIT 3;

LIMIT works in SQLite, PostgreSQL and MySQL. Some other databases write it differently, such as SELECT TOP 3 … in SQL Server, or FETCH FIRST 3 ROWS ONLY at the end in Oracle, but the idea is the same.

Typical mistake: LIMIT before ORDER BY. LIMIT is always the last part of the query, so … LIMIT 3 ORDER BY price; ends with the error near "ORDER": syntax error.