Loading…
Loading…
SQL tutorial · Beginner
⏱️ 5 mins read
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.
SELECT cols FROM table LIMIT n;
SELECT cols FROM table LIMIT n OFFSET k;-- 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;| 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 |
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;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 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;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.
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.
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?
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 →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).
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.