SQL Query Row Limits and Offsets Oracle Database 12C release 1 (12.1)

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.

Description of row_limiting_clause.gif follows

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *