SQL LIMIT and OFFSET
beginnerbasics
2 min read
What are LIMIT and OFFSET?
LIMIT restricts the number of rows a query returns. OFFSET skips a specified number of rows before starting to return results. Together they enable pagination.
Syntax
SELECT column1, column2
FROM table_name
ORDER BY column1
LIMIT count
OFFSET skip;When to Use
- Paginating results in a web application (page 1, page 2, etc.)
- Fetching the top N records
- Sampling a few rows from a large table
Key Points
- Always Pair with ORDER BY — Without
ORDER BY, the set of rows returned byLIMITis unpredictable. - OFFSET Starts at 0 —
OFFSET 0is the same as no offset.OFFSET 10skips the first 10 rows. - Performance — Large
OFFSETvalues can be slow because the database must scan and discard rows. Keyset pagination is faster for deep pages. - PostgreSQL Extension —
LIMITandOFFSETare widely supported but are not part of the SQL standard. The standard alternative isFETCH FIRST n ROWS ONLY. - Combining —
LIMIT 10 OFFSET 20returns rows 21 through 30.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL LIMIT and OFFSET Challenge
Write a query that solve this task: return only the first 5 rows from the students table.
Expected result
The first 5 students ordered by id.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are LIMIT and OFFSET?
Initializing database...Each run starts from fresh sample data.
More Examples
Paginate results (page 2)
Skip the first 10 rows and return the next 10.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What happens if OFFSET is larger than the number of rows?
The query returns an empty result set. No error is raised.
Is LIMIT part of standard SQL?
LIMIT is a PostgreSQL and MySQL extension. The SQL standard uses FETCH FIRST n ROWS ONLY, which PostgreSQL also supports.