FETCH FIRST N ROWS
beginnerfiltering
2 min read
What is FETCH FIRST?
FETCH FIRST N ROWS ONLY is the SQL-standard way to limit the number of rows returned by a query. It is functionally equivalent to PostgreSQL's LIMIT clause but is more portable across database systems.
Syntax
SELECT * FROM table_name
ORDER BY column
FETCH FIRST 10 ROWS ONLY;With OFFSET:
SELECT * FROM table_name
ORDER BY column
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY;When to Use
- When writing standard-compliant SQL
- Pagination alongside OFFSET
- When you prefer the readability of FETCH FIRST over LIMIT
Key Points
- SQL Standard — FETCH FIRST is part of the SQL:2008 standard.
- Equivalent to LIMIT —
FETCH FIRST 10 ROWS ONLYequalsLIMIT 10. - OFFSET — Use OFFSET N ROWS before FETCH FIRST for pagination.
- WITH TIES —
FETCH FIRST 10 ROWS WITH TIESincludes extra rows that have the same sort value as the last row. - ORDER BY Recommended — Always use ORDER BY with FETCH FIRST for deterministic results.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
FETCH FIRST N ROWS Challenge
Write a query that solve this task: get the 5 youngest students.
Expected result
The 5 students with the lowest age values.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is FETCH FIRST?
Stuck? Reveal a hint:
Starting engine…
Press Run to execute the query once the engine is ready.
More Examples
Fetch with OFFSET
Skip the first 10 rows and fetch the next 5.
Starting engine…
Press Run to execute the query once the engine is ready.
Fetch with TIES
Get top 3 students by age, including ties.
Starting engine…
Press Run to execute the query once the engine is ready.
Frequently Asked Questions
What is the difference between FETCH FIRST and LIMIT?
They produce the same result. FETCH FIRST is SQL-standard; LIMIT is PostgreSQL/MySQL-specific. FETCH FIRST also supports WITH TIES.
What does WITH TIES do?
WITH TIES includes additional rows that have the same ORDER BY value as the last row in the result set, so you may get more rows than requested.