Oracle 12c now supports top-N SQL queries (those that return limited number of rows after sort operation). In previous releases this was performed by limiting return by the pseudo column ROWNUM before the ORDER BY clause but was not a true top-N query. This has been resolved with two new clauses: FETCH FIRST and OFFSET.
Examples:
FETCH FIRST – the example below use FETCH FIRST to return the 3 highest salaried persons from scott.emp.
- Logon to SQLPLUS as SCOTT.
[oracle@linux2 ~]$ sqlplus scott/password
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 4 07:45:52 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jan 03 2016 12:44:03 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Execute the query below:
select ename, sal from scott.emp order by sal desc
fetch first 3 rows only;
SQL> select ename, sal from scott.emp order by sal desc
fetch first 3 rows only;
2
ENAME SAL
———- ———-
KING 5000
SCOTT 3000
FORD 3000
SQL>
- Use ASC clause to get the reverse order, as below
select ename, sal from scott.emp order by sal asc
fetch first 3 rows only;
SQL> select ename, sal from scott.emp order by sal asc
fetch first 3 rows only;
2
ENAME SAL
———- ———-
SMITH 800
JAMES 950
DOE 1000
SQL>
- FETCH LAST clause does not exist must use ASC FETCH FIRST clause.
OFFSET – Skips a number of rows in the ordered list and with FETCH NEXT gives a range of rows.
- Logon to SQLPLUS as SCOTT.
[oracle@linux2 ~]$ sqlplus scott/password
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 4 07:45:52 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jan 03 2016 12:44:03 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Execute the query below:
select ename, sal from scott.emp order by sal desc offset 3 rows fetch next 3 rows only;
SQL> select ename, sal from scott.emp order by sal desc offset 3 rows fetch next 3 rows only;
ENAME SAL
———- ———-
JONES 2975
BLAKE 2850
CLARK 2450
SQL>
FETCH NEXT can also give a percentage say you want to know the top 10% of all salaries.
- Logon to SQLPLUS as SCOTT.
[oracle@linux2 ~]$ sqlplus scott/password
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 4 07:45:52 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jan 03 2016 12:44:03 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Execute the query below:
select ename, sal from scott.emp order by sal desc fetch next 10 percent rows only;
SQL> select ename, sal from scott.emp order by sal desc fetch next 10 percent rows only;
ENAME SAL
———- ———-
KING 5000
FORD 3000
SQL>
- This completes using row limit clauses in Oracle 12c.
Larry Catt
OCP