FETCH Clause

The FETCH clause specifies the number of rows to return. If no FETCH clause is specified, then SQL returns all rows.

FETCH follows this syntax. FETCH {FIRST||NEXT} {count} {ROW|ROWS} ONLY

• FIRST

  • : Returns the count of rows beginning from the first row in the dataset.

• NEXT

  • : Returns the count of rows that immediately follow the current row.

• count

  • : Optional. The number of rows to return. If you don't specify a count, FETCH returns one row by default.

note: FETCH FIRST ROW ONLY is the same as FETCH FIRST 1 ROW ONLY.

In this example, because there’s an offset of one row, FETCH starts from the second value of the City column, ordered ascending. The query returns the next three rows, which are the second, third, and fourth rows.

SELECT City
FROM "Superstore"
OFFSET 1
ORDER BY City
FETCH NEXT 3 ROWS ONLY;

Here, the query returns the first row.

SELECT City
FROM "Superstore"
ORDER BY City
FETCH FIRST ROW ONLY;