Fetch!

In 12c, Oracle released the Fetch/Offset clauses of the Select statement. It can replace "WHERE ROWNUM <". Besides having more functionality, it also leaves the WHERE statement to true conditions.

Some things to keep in mind:

• 'First' is the same as 'Last'. Use whichever reads better.
• 'Row' is the same as 'Rows'. Ditto
• If you don't use ORDER BY, records are typically returned in the order in which they were inserted into the table.
• You can use all of these in subqueries.
• Percent doesn't round, it truncates. (For example, if you ask for 30% of a table with 10 records, you get exactly 3 records.)
• Offset used by itself (without the Fetch), will pull from that record to the end of the table.

Display First Row Only

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
 FETCH FIRST ROW ONLY;

Or First 5

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
 FETCH FIRST 5 ROWS ONLY; 

Display Next 5

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Or 2%

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
 ORDER BY employee_id
 FETCH FIRST 2 PERCENT ROWS ONLY;

With ties

Here we just ask for 2 records, but record 2 and 3 tie with the same salary so 3 records are displayed.

SELECT employee_id, first_name, last_name, job_id, salary
  FROM hr.employees
  WHERE department_id = 60
 ORDER BY salary
 FETCH FIRST 2 ROWS WITH TIES ;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	JOB_ID	SALARY
107	Diana	Lorentz	IT_PROG	4200
105	David	Austin	IT_PROG	4800
106	Valli	Pataballa	IT_PROG	4800