ROW LIMITING FOR TOP-N RESULT QUERIES

ROW LIMITING FOR TOP-N RESULT QUERIES

There are various indirect approaches/methods exist to fetch Top-N query results for top/bottom rows in the previous releases. In 12c, retrieving Top-N query results for top/bottom rows simplified and become straight forward with the new FETCH FIRST|NEXT|PERCENT clauses.

In order to retrieve top 10 salaries from EMP table, use the following new SQL statement:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY;

The following example fetches all similar records of Nth row. For example, if the 10th row has salary of 5000 value, and there are other employees whose salary matches with the Nth value, the will also be fetched upon mentioningWITH TIES clause.

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY WITH TIES;

The following example limits the fetch to 10 per cent from the top salaries in the EMP table:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 PERCENT ROWS ONLY;

The following example offsets the first 5 rows and will display the next 5 rows from the table:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

All these limits can be very well used within the PL/SQL block too.

BEGIN

SELECT sal BULK COLLECT INTO sal_v FROM EMP

FETCH FIRST 100 ROWS ONLY;

END;