New keywords OFFSET and FETCH provides ANSI SQL standard to limit the number of rows returned and to specify a starting row for the return set.
Note
- ROW and ROWS has got the same meaning and can be used interchangeably
- FIRST and NEXT has got the same meaning and can be used interchangeably
Test data
create table test_tbl ( id number, id1 number ); begin for i in 1..10 loop insert into test_tbl values(i, mod(i,5)); end loop; commit; end; / select * from test_tbl order by id; ID ID1 ---------- ---------- 1 1 2 2 3 3 4 4 5 0 6 1 7 2 8 3 9 4 10 0 10 rows selected
OFFSET
The keyword is used to specify the number of rows to skip before row limiting begins.
--skip first 7 fetched records select * from test_tbl order by id offset 7 rows; ID ID1 ---------- ---------- 8 3 9 4 10 0 --skip first 7 fetched records select * from test_tbl order by id offset 7 row; ID ID1 ---------- ---------- 8 3 9 4 10 0 --fractions are ignored so 6.9 is changed to 6 select * from test_tbl order by id offset 6.9 rows; ID ID1 ---------- ---------- 7 2 8 3 9 4 10 0 NOTE - order by clause is not required. It's specified to get consistent results. Oracle doesn't guarantee the same results without order by.
- OFFSET must be number
- If negative then is treated as 0
- If NULL or greater than number of fetched rows then returns 0 rows
- if includes fraction, fraction is truncated
ROW | ROWS – can be used interchangeably and are provided for semantic clarity
FETCH
Use this clause to specify the number of rows or percentage of rows to return. If you do not specify this clause, then all rows are returned, beginning at row OFFSET + 1.
FIRST | NEXT – can be used interchangeably and are provided for semantic clarity
--just fetch first 3 rows select * from test_tbl order by id fetch next 6 rows only; ID ID1 ---------- ---------- 1 1 2 2 3 3 4 4 5 0 6 1 --just fetch first 3 rows select * from test_tbl order by id fetch first 3 rows only; ID ID1 ---------- ---------- 1 1 2 2 3 3 --just fetch 30% of rows select * from test_tbl order by id fetch next 30 percent rows only; ID ID1 ---------- ---------- 1 1 2 2 3 3 --just fetch 30% of rows select * from test_tbl order by id fetch first 30 percent row only; ID ID1 ---------- ---------- 1 1 2 2 3 3 NOTE - order by clause is not required. It's specified to get consistent results. Oracle doesn't guarantee the same results without order by.
Special meaning is for WITH TIES. It’s used to return additional rows with the same sort key as the last row fetched.
--should return 1 record but because of with ties returns more select * from test_tbl order by id1 fetch first 1 rows with ties; ID ID1 ---------- ---------- 5 0 10 0 select * from test_tbl order by id1 fetch first 2 rows with ties; ID ID1 ---------- ---------- 5 0 10 0 select * from test_tbl order by id1 fetch first 3 rows with ties; ID ID1 ---------- ---------- 5 0 10 0 1 1 6 1 NOTE - order by defines sort key. If ORDER BY is not specified then it WITH TIES works like ONLY select * from test_tbl fetch next 3 rows with ties; ID ID1 ---------- ---------- 1 1 2 2 3 3
Combination of OFFSET and NEXT
--skip first 3 records and returns next 3 select * from test_tbl order by id offset 3 rows fetch first 3 row only; ID ID1 ---------- ---------- 4 4 5 0 6 1 --the same as above select * from test_tbl order by id offset 3 row fetch next 3 row only; ID ID1 ---------- ---------- 4 4 5 0 6 1 --skip first 3 rows and returns next 4 rows, but because --of with ties it returns 5 records select * from test_tbl order by id1 offset 3 row fetch next 4 row with ties; ID ID1 ---------- ---------- 6 1 2 2 7 2 3 3 8 3
Have a fun 🙂
Tomasz