Oracle Database 12c now includes a row_limiting_clause in the SELECT statement. This new clause limits the rows returned in a more meaningful and effective way, compared to the methods available in previous releases [using ROWNUM or using Top-N rows using subqueries or using analytic functions like RANK or DENSE_RANK].
The syntax for the new clause according to the Oracle documentation is:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
the row_limiting_clause follows the ORDER BY clause in the SELECT statement, though ORDER BY is not mandatory, but is recommended for consistent results if you are looking for Top-N analysis.
OFFSET clause is used to skip the specified number of rows before the limiting begins. If the offset is higher than the number of rows retrieved or is NULL, no rows are returned. ROW, ROWS keywords are there only for readability.
FETCH clause can specify the number of rows to return or a percentage of rows to return. FIRST, NEXT keywords can be used interchangeably and is for semantic clarity only.
ONLY specifies to return exact number or percent of rows to return.
WITH TIES specifies to return all rows that have the same sort keys as the last row of the row-limited result set (requires an ORDER BY clause).
Examples:
SQL> select * from jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> select * from jobs order by min_salary desc
fetch first 5 rows only;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
SA_MAN Sales Manager 10000 20080
MK_MAN Marketing Manager 9000 15000
FI_MGR Finance Manager 8200 16000
5 rows selected.
SQL> select * from jobs order by min_salary desc
fetch first 5 rows with ties;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
SA_MAN Sales Manager 10000 20080
MK_MAN Marketing Manager 9000 15000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
6 rows selected.<br>
SQL> select * from jobs order by min_salary desc
offset 3 rows fetch first 5 rows with ties;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
MK_MAN Marketing Manager 9000 15000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
PU_MAN Purchasing Manager 8000 15000
SA_REP Sales Representative 6000 12008
5 rows selected.<br>
SQL> select * from jobs order by min_salary desc
fetch first 20 percent rows only;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
SA_MAN Sales Manager 10000 20080
MK_MAN Marketing Manager 9000 15000
4 rows selected.
SQL> select * from jobs order by min_salary desc
offset 3 rows fetch first 20 percent rows with ties;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
MK_MAN Marketing Manager 9000 15000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
PU_MAN Purchasing Manager 8000 15000
4 rows selected.
