Top N Number Query (Oracle FETCH)

FETCH Function in Oracle

Topic Introduction: Here we will discuss the use of the FETCH function in Oracle clause to return limit row by SQL Query.

The FETCH statement retrieves rows of data from the result set of a multiple-row query. one row at a time, several rows at a time, or all rows at once.

Ex.

SELECT last_name, job_id, salary

  FROM employees

ORDER BY salary DESC

FETCH NEXT 5 ROWS ONLY;

This query will return the top 5 salaries paid to employees using the FETCH clause.

Oracle FETCH clause syntax

[ OFFSET offset ROWS]

 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

OFFSET clause: In this clause, we can declare the number of rows to avoid before the low limiting starts. OFFSET clause is optional, we can skip this. If we skip this clause offset will be 0 and row limiting starts from 1st row. The offset must be a number or an expression that evaluates to a number.

1. If the offset is negative, then it is treated as 0.

2. If the offset is NULL, then no row is returned.

3. If the offset is greater than the number of rows returned by the query, then no row is returned.

4. If the offset includes a fraction, then the fractional portion is truncated.

 

FETCH clause: The clause declares the number of rows or percentage of rows that want to return.

Here ROW & ROWS, FIRST & NEXT work the same. So don’t hesitate for using this keyword.

Ex.

FETCH NEXT 1 ROWS

FETCH FIRST 1 ROW

ONLY | WITH TIES: The ONLY keyword returns exactly the number of rows or percentage of rows after FETCH NEXT (or FIRST).

The WITH TIES Keyword returns additional rows with the same sort key as the last row fetched. Note that if you use WITH TIES, we must need an ORDER BY clause in the query. If we don’t use ORDER BY, the query will not return the additional rows.

 

Example of Top N Row (Salary)

SELECT last_name, job_id, salary

  FROM employees

ORDER BY salary DESC

FETCH NEXT 3 ROWS ONLY;

 

This query will return the top 3 salaries paid to employees using the FETCH clause.

 

Example of Limit by the percentage of rows(Salary)

 

SELECT last_name, job_id, salary

  FROM employees

ORDER BY salary DESC

FETCH NEXT 3 PERCENT ROWS ONLY;

 This query will return the top 3 Percent salary paid employee using the FETCH clause with PERCENT ROWS.

Example of WITH TIES

 

SELECT last_name, job_id, salary

  FROM employees

ORDER BY salary DESC

FETCH NEXT 2 ROWS WITH TIES;

This query will return the top 2 salaries paid to employees using the FETCH clause. Here notice that FETCH NEXT 2 ROWS WITH TIES but returns 3 rows because of using WITH TIES.

Example of OFFSET 

SELECT last_name, job_id, salary

  FROM employees

ORDER BY salary DESC

OFFSET 3 ROWS 

FETCH NEXT 5 ROWS ONLY;

In this query 1st, the top three will be skipped and shown after 5  top rows (4 to 8).