LIMIT & OFFSET

Beginner

⏱️ 5 mins read

Pages and takes top-N over the employees table from Topic 1.

What You'll Learn

LIMIT restricts the number of rows returned. OFFSET skips rows — essential for pagination. Always combine with ORDER BY when paginating to ensure consistent results. SQL Server uses TOP instead of LIMIT. Oracle uses FETCH FIRST n ROWS ONLY.

Syntax

SELECT cols FROM table LIMIT n;
SELECT cols FROM table LIMIT n OFFSET k;

Example

-- First 5 rows
SELECT * FROM employees LIMIT 5;

-- Page 2 of results (5 per page)
SELECT * FROM employees
ORDER BY name
LIMIT 5 OFFSET 5;

-- Top 3 earners
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- id | name    | department_id | salary | hire_date
-- 1  | Ada     | 1             | 145000 | 2021-03-01
-- 2  | Grace   | 1             | 115000 | 2022-06-13
-- 3  | Alan    | 2             | 92000  | 2020-11-02
-- 4  | Edsger  | NULL          | 78000  | 2023-08-19
-- 5  | Barbara | 3             | 64000  | 2024-01-08

Top-N without ties needs more than LIMIT

LIMIT 5 returns five arbitrary rows when there are ties at the cutoff — 'top 5 salaries' with two people tied at the boundary silently drops one of them. Standard SQL answers this with FETCH FIRST n ROWS WITH TIES, which keeps every row that ties the last included one.

-- Ties at the cutoff get arbitrarily truncated:
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Include rows tied with the 5th (PostgreSQL 13+, SQL Server, Oracle):
SELECT name, salary FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;
LIMIT answers 'give me 5 rows'; WITH TIES answers 'give me the top 5 values.' Decide which question the business is actually asking.

OFFSET pagination silently breaks on live data

OFFSET is positional: it counts rows in the moment the query runs. If rows are inserted or deleted between page requests, every later page shifts — users see the same employee twice, or miss one entirely. Keyset ('cursor') pagination is immune because it anchors on values, not positions.

-- Page 2 ordered by hire date. Add one new hire, and this
-- same query now returns a DIFFERENT set of rows:
SELECT id, name FROM employees
ORDER BY hire_date, id
LIMIT 10 OFFSET 10;

-- Keyset pagination: stable no matter what gets inserted
SELECT id, name FROM employees
WHERE (hire_date, id) > ('2023-08-19', 4)   -- values of the last row shown
ORDER BY hire_date, id
LIMIT 10;
OFFSET is fine for stable admin screens; anything user-facing, scheduled, or exported should paginate by key.

Deep OFFSET is O(offset) — the engine skips row by row

OFFSET 100000 does not jump; the engine reads and discards 100,000 rows before producing yours. Cost grows linearly with page depth — which is why page 1 is fast and page 5,000 times out on the same query.

-- Both return 10 rows, but the second one scans 100,010 to do it:
SELECT id, name FROM employees ORDER BY id LIMIT 10;
SELECT id, name FROM employees ORDER BY id LIMIT 10 OFFSET 100000;
If pages can go deep, keyset pagination is not just more correct — it is dramatically cheaper, because there is nothing to skip.

Common Mistakes

Using LIMIT/OFFSET without ORDER BY for pagination. Without ORDER BY the 'page' is non-deterministic — you may see duplicate or missing rows across pages.

Interview Tips

For very large offsets (OFFSET 10000000), performance degrades because the DB still scans all skipped rows. Mention keyset pagination (WHERE id > last_seen_id) as a better alternative for large datasets.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. ORDER BY salary DESC LIMIT 5 — two employees tie at the cutoff. What happens?

2. Why does OFFSET pagination break on live data?

3. Why is LIMIT 10 OFFSET 100000 slow?

4. What makes keyset ('cursor') pagination stable where OFFSET is not?

5. Which clause is the SQL-standard spelling of LIMIT?

6. What must almost every pagination query include to be correct?

Practice

Return the 4th-highest distinct transaction amount — skip the top three amounts with OFFSET, keep one row with LIMIT.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

How do I get the 2nd highest salary?

The quick way: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), or ORDER BY salary DESC LIMIT 1 OFFSET 1. But both break on ties — the robust interview answer is DENSE_RANK() OVER (ORDER BY salary DESC) and filtering rank = 2 (Topic 18).

Is LIMIT standard SQL?

No. The standard spells it FETCH FIRST n ROWS ONLY. LIMIT is PostgreSQL/MySQL/SQLite, TOP n is SQL Server, and Oracle historically used ROWNUM. Know your dialect — and know the standard name, because interviewers do.

Back to Full Roadmap