FETCH FIRST N ROWS

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

1. SQL Standard — FETCH FIRST is part of the SQL:2008 standard.
2. Equivalent to LIMIT — FETCH FIRST 10 ROWS ONLY equals LIMIT 10.
3. OFFSET — Use OFFSET N ROWS before FETCH FIRST for pagination.
4. WITH TIES — FETCH FIRST 10 ROWS WITH TIES includes extra rows that have the same sort value as the last row.
5. ORDER BY Recommended — Always use ORDER BY with FETCH FIRST for deterministic results.