SQL> select ename emp
where rownum < 6
order by 1;
Ename
--------------------
RaJa
vanaja
Rateendra
Sundar
Alexander
But as you can see, this doesn’t work as Oracle first selects the rows, THEN orders them. This type of problem can be surprisingly difficult to solve.
Oracle 12c, however, introduces new SELECT clauses: FETCH, OFFSET, and PERCENT. These new
clauses allow you to easily find and solve Top-N type queries easily. Let’s look at the FETCH keyword first.
FETCH allows you to define a query where only the first or last N number of rows are returned. Let’s look
at a simple query:
SQL> select first_name from hr.employees
2 order by 1
3 fetch first 5 rows only;
Ename
--------------------
RaJa
vanaja
Rateendra
Sundar
Alexander
As you can see, unlike the earlier query that used the ROWNUM clause, FETCH accurately returns the first five rows ordered by first_name. We can also use the LAST keyword to return the last N number of rows
from a SELECT:
SQL> select ename from emp
2 order by 1
3 fetch last 5 rows only;
FIRST_NAME
--------------------
Valli Vijayan
Vincent
Rudra
Sita
Ruderford
