FETCH

Supported in Oracle 12c and above, used to limit the number of rows returned by queries and implement pagination, equivalent to MySQL’s LIMIT.

Full Syntax

[ OFFSET offset ROWS ]
FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]Code language: CSS (css)

1. OFFSET Clause (Optional)

Purpose: Skip the first N rows, mainly for pagination scenarios.

• When omitted, offset equals 0, reading data starting from the first row
• Negative offset: treated as value 0
• Offset is NULL or larger than total rows: zero rows will be returned
• Offset with decimals: decimal part will be truncated, only integer part remains

2. FETCH Clause

Purpose: Define how many rows to return, supports row‑count and percentage modes.

• ONLY: Return exact specified rows (most frequently‑used)
• WITH TIES: Return extra rows which share identical sort‑field value with the last fetched row. Must work together with ORDER BY

Sample Codes

Fetch top 5 records, sorted by name descending

SELECT *
FROM student
ORDER BY name DESC
FETCH NEXT 5 ROWS ONLY;

Fetch first 2 rows

select * from student fetch next 2 rows only;Code language: JavaScript (javascript)

Output: Returns first two records jim, lisa.

Pagination: skip 3 rows, read 4 rows (Page 2, 4 items per page)

SELECT *
FROM student
ORDER BY id
OFFSET 3 ROWS
FETCH NEXT 4 ROWS ONLY;

Return by percentage, fetch 10% of total dataset

SELECT *
FROM student
FETCH NEXT 10 PERCENT ROWS ONLY;

WITH TIES usage (include tied ranking records)

SELECT *
FROM student
ORDER BY score DESC
FETCH NEXT 3 ROWS WITH TIES;

If multiple records hold same score as the 3rd place, all matching rows will be output, actual result count may exceed 3.

FETCH