FETCH in Oracle

Posted by AbundantCode

You’ll learn how to utilise the Oracle FETCH clause to limit the number of rows returned by a query in this article.

The LIMIT clause in some RDBMS, such as MySQL and PostgreSQL, allows you to receive only a subset of the results created by a query.

The LIMIT clause is used in the following query to return the top 5 products with the highest inventory level:

SELECT
	product_name,
	quantity
FROM
	inventories
INNER JOIN products
		USING(product_id)
ORDER BY
	quantity DESC 
LIMIT 5;

The ORDER BY clause in this example organises the products in descending order by stock quantity, while the LIMIT clause returns only the top 5 products with the highest stock quantity.

The LIMIT clause does not exist in Oracle Database. Since the release of Oracle 12c, it has included a similar but more versatile clause known as the row limiting clause.

You can modify the query that uses the LIMIT clause above using the row limiting clause as follows:

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;

Syntax of the Oracle FETCH clause

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

OFFSET Clause

The OFFSET parameter defines how many rows should be skipped before row limiting begins. The OFFSET clause is not required. If you skip it, offset will be 0 and row limiting will begin with the first row.

A number or an expression that evaluates to a number must be used as the offset. The following rules apply to the offset:

  • If the offset is negative, it is treated as if it were zero.
  • No row is returned if the offset is NULL or more than the number of rows returned by the query.
  • The fractional component of the offset is trimmed if it contains a fraction.

FETCH Clause

The number of rows or percentage of rows to return is specified by the FETCH clause.

You can use the keywords ROW instead of ROWS and FIRST instead of NEXT for semantic clarity. The following sentences, for example, behave in the same way: You can use the keywords ROW instead of ROWS and FIRST instead of NEXT for semantic clarity. The following sentences, for example, behave in the same way:

FETCH NEXT 1 ROWS
FETCH FIRST 1 ROW

ONLY WITH TIES

After FETCH NEXT, ONLY delivers the exact number of rows or percentage of rows (or FIRST).

Additional rows with the same sort key as the last row fetched are returned by the WITH TIES. You must include an ORDER BY clause in the query if you utilise WITH TIES. The query will not return the additional rows if you don’t.

FETCH clause examples in Oracle

Select Top N Rows

The top ten products with the highest inventory level are returned by the following statement:

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 10 ROWS ONLY;

WITH Ties Example

The WITH TIES option is used with the row limiting clause in the following query:

SELECT
	product_name,
	quantity
FROM
	inventories
INNER JOIN products
		USING(product_id)
ORDER BY
	quantity DESC 
FETCH NEXT 10 ROWS WITH TIES;

Because the query used the WITH TIES option, it returned two more rows, despite the fact that the query only required ten results. It’s worth noting that the quantity column in these two extra rows is the same as the quantity column in row 10.

Limit by Percentage

The following query gives the top 5% of products with the most inventory:

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH FIRST 5 PERCENT ROWS ONLY;

OFFSET

The following query provides the next ten products after skipping the top ten with the highest inventory level:

SELECT
	product_name,
	quantity
FROM
	inventories
INNER JOIN products
		USING(product_id)
ORDER BY
	quantity DESC 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;
Tags: