QuerydrillLIMIT and OFFSET
LIMIT n returns at most n rows and OFFSET m skips the first m rows: SELECT * FROM products ORDER BY price DESC LIMIT 10 gives the ten most expensive products. Always pair LIMIT with ORDER BY, otherwise which rows you get is undefined. SQL Server uses TOP and Oracle uses FETCH FIRST instead.
Syntax
SELECT columns FROM table ORDER BY column LIMIT count [OFFSET skip];| Part | What it is |
|---|---|
count | Maximum number of rows to return |
skip | Number of rows to skip before returning rows (default 0) |
Examples
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;Result: The three highest paid employees
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 40;Result: Rows 41 to 60: page 3 when pages hold 20 rows
Offset for page p is (p - 1) * page_size.
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;Result: The second highest distinct salary
DISTINCT matters: if two people share the top salary, OFFSET 1 without DISTINCT would return the top salary again.
Common mistakes
- LIMIT without ORDER BY returns an arbitrary set of rows, so the top 10 may change between runs; add ORDER BY.
- Writing LIMIT before ORDER BY is a syntax error; LIMIT is always the last clause.
- Using OFFSET for deep pagination (OFFSET 100000) is slow because the database still reads and discards the skipped rows; use keyset pagination (WHERE id > last_seen_id) for large tables.
Practice LIMIT and OFFSET
These drills check your answer on live data, free, no account needed.
- Top three earnersReturn the name and salary of the three highest-paid employees, highest salary first.
- Second highest salaryReturn the second highest salary as a single value.
- Third highest salaryReturn the third highest distinct salary as a single value.
Questions
How do I get the top 10 rows in SQL?
Sort and limit: SELECT * FROM table ORDER BY score DESC LIMIT 10. In SQL Server use SELECT TOP 10 ... ORDER BY score DESC, in Oracle FETCH FIRST 10 ROWS ONLY.
How do I get the second highest salary?
ORDER BY salary DESC LIMIT 1 OFFSET 1 with DISTINCT, or use a subquery: WHERE salary < (SELECT MAX(salary) FROM employees) with MAX. For the nth highest, DENSE_RANK() is the most robust.
Related
- ORDER BYORDER BY sorts the rows of a query result. Learn ASC and DESC, sorting on multiple columns, sorting by an expression or alias, and where NULLs end up.
- ROW_NUMBERROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3 to rows; with PARTITION BY it restarts per group. Latest row per customer, remove duplicates, paginate.
- RANK and DENSE_RANKRANK() and DENSE_RANK() rank rows and give ties the same rank; RANK skips numbers after a tie, DENSE_RANK does not. Nth highest salary and top N per group.
- SubqueryA subquery is a SELECT inside another query. Scalar subqueries (compare to an average), IN subqueries, correlated subqueries and derived tables in FROM.