Oracle 12c SQL: New Features Top N queries using FETCH FIRST and OFFSET

This feature can be really useful for ETL and reporting purpose. Oracle 12c eases the cases of top-N analysis by introducing FETCH FIRST clause in SQL language. The clause internally sorts the query result set and retrieves the specified number of rows from the set. There are two flavors of pulling out the data from the result set i.e. either retrieve fixed number of rows (FETCH FIRST 10 ROWS ONLY) or retrieve a percentage of rows from the result set (FETCH 5 PERCENT ONLY). If one needs to retrieve the data set after excluding a certain number of rows from the top, OFFSET clause can be used. If more than one row in the set satisfies the fetch condition, retrieve all the rows obeying the boundaries using WITH TIES clause.

The below SQL query fetches top-5 employees sorted by their salary, in the company.

SELECT employee_id, last_name
FROM employees
ORDER BY salary
FETCH FIRST 5 ROWS ONLY ;

The below SQL query fetches top 5% employees sorted by their salary and includes the those with the same salary.

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES ;

-----------------------------------------

SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

the starting point for the FETCH is OFFSET+1.