Limiting Results

Tables get large. Most of the time you want a handful of rows, not all of them.

LIMIT

SQL▶ Run
SELECT first_name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3;

The three highest-paid employees.

LIMIT without ORDER BY is a coin toss. "Any 3 rows" is what you asked for, and the answer may change between runs. Sort first, then limit.

OFFSET — skipping rows

SQL▶ Run
SELECT first_name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

Rows four to six: page two, if a page is three rows.

The dialects differ, and this one bites

OFFSET … FETCH is the ANSI standard, and PostgreSQL accepts it too. If you want one query that moves between databases, prefer it.

Ties: LIMIT does not know about them

SQL▶ Run
SELECT first_name, last_name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 2;

Two employees share a salary of 88000 in this table. LIMIT 2 returns whichever two the database happened to order first, and it may not be the same two next time. When ties matter, add a tiebreaker column — ORDER BY salary DESC, emp_id — or use RANK(), which the window-functions module covers.

OFFSET gets slow

OFFSET 100000 makes the database produce 100,000 rows and throw them away. Deep pagination on a large table is a well-known performance trap. The usual fix is keyset pagination: remember the last row you saw and ask for rows after it.

SQL▶ Run
SELECT emp_id, first_name
FROM employees
WHERE emp_id > 5
ORDER BY emp_id
LIMIT 3;

That reads three rows however far into the table you are.

Common mistakes

• LIMIT with no ORDER BY — an arbitrary subset presented as "the top N".
• Assuming LIMIT handles ties — it truncates; it does not consider equality.
• Deep OFFSET on a big table — increasingly slow, and the usual cause of a slow "page 500".

Interview question

How do you get the second-highest salary?

ORDER BY salary DESC LIMIT 1 OFFSET 1 works — until two people share the top salary, at which point it returns the top salary again. DENSE_RANK() answers the question that was actually asked. This is one of the most common SQL interview questions, and the tie is the entire point of it.

Check yourself

1. Why is LIMIT without ORDER BY unreliable?
2. Write the SQL Server equivalent of LIMIT 10 OFFSET 20.
3. Why does deep OFFSET get slow, and what replaces it?

Next

Next: Query execution order — the sequence the database really uses, and the mistakes it explains.