Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle FETCH

Oracle FETCH

Summary: in this tutorial, you will learn how to use the Oracle FETCH clause to limit the rows returned by a query.

Introduction to Oracle FETCH clause

Some RDBMS such as MySQL and PostgreSQL have the LIMIT clause that allows you to retrieve a portion of rows generated by a query.

See the following products and inventories tables in the sample database.

products and inventories tables

The following query uses the LIMIT clause to get 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;

In this example, the ORDER BY clause sorts the products by stock quantity in descending order and the LIMIT clause returns only the first 5 products with the highest stock quantity.

Oracle Database does not have the LIMIT clause. However, since 12c release, it provided a similar but more flexible clause named row limiting clause.

By using the row limiting clause, you can rewrite the query that uses the LIMIT clause above as follows:

SELECT product_name, quantity FROM inventories INNER JOIN products USING(product_id) ORDER BY quantity DESC FETCH NEXT 5 ROWS ONLY;
Oracle FETCH - top 5 products with highest inventory level

In this statement, the row limiting clause is:

FETCH NEXT 5 ROWS ONLY

Similar to the statement that uses LIMIT clause above, the row limiting clause returns the top 5 products with the highest inventory level.

Oracle FETCH clause syntax

The following illustrates the syntax of the row limiting clause:

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

 OFFSET clause

The OFFSET clause specifies the number of rows to skip before the row limiting starts. The OFFSET clause is optional. If you skip it, then offset is 0 and row limiting starts with the first row.

The offset must be a number or an expression that evaluates to a number. The offset is subjected to the following rules:

  • If the offset is negative, then it is treated as 0.
  • If the offset is NULL or greater than the number of rows returned by the query, then no row is returned.
  • If the offset includes a fraction, then the fractional portion is truncated.

 FETCH clause

The FETCH clause specifies the number of rows or percentage of rows to return.

For the semantic clarity purpose, you can use the keyword ROW instead of ROWS, FIRST instead of  NEXT. For example, the following clauses behavior the same:

FETCH NEXT 1 ROWS FETCH FIRST 1 ROW

 ONLY | WITH TIES

The ONLY returns exactly the number of rows or percentage of rows after FETCH NEXT (or FIRST).

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

Oracle FETCH clause examples

A) Top N rows example

The following statement returns the top 10 products with the highest inventory level:

SELECT product_name, quantity FROM inventories INNER JOIN products USING(product_id) ORDER BY quantity DESC FETCH NEXT 10 ROWS ONLY;
Oracle FETCH - top 10 products with highest inventory level

B) WITH TIES example

The following query uses the row limiting clause with the WITH TIES option:

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

Even though the query requested 10 rows, because it had the WITH TIES option, the query returned two more additional rows. Notice that these two additional rows have the same value in the quantity column as the row 10.

C) Limit by percentage of rows example

The following query returns top 5% products with the highest inventory level:

SELECT product_name, quantity FROM inventories INNER JOIN products USING(product_id) ORDER BY quantity DESC FETCH FIRST 5 PERCENT ROWS ONLY;
Oracle FETCH - Percentage of rows example

The inventories table has 1112 rows, therefore, 5% of 1112 is 55.6 which is rounded up to 56 (rows).

D) OFFSET example

The following query skips the first 10 products with the highest level of inventory and returns the next 10 ones:

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

In this tutorial, you have learned how to use the Oracle FETCH clause to limit rows returned by a query.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Alias
Next Oracle AND Operator

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.