OracleDB12c New Feature: New Row Limiting Clause in SELECT

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.