3 Oracle LIMIT Clause Alternatives

Many RDBMSs have a LIMIT clause that allows us to limit the rows returned by a query to a specified number. SQL Server doesn’t have a LIMIT clause but it does have a TOP() function that allows us to do the same thing.

But Oracle Database has neither.

Fortunately, Oracle does have a number of options that allow us to get the same result.

Sample Data

First, the examples on this page use the following data:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId;

Result:

PRODUCTID	PRODUCTNAME	PRODUCTPRICE
1	Left handed screwdriver	25.99
2	Right handed screwdriver	25.99
3	Long Weight (blue)	14.75
4	Long Weight (green)	11.99
5	Sledge Hammer	33.49
6	Chainsaw	245
7	Straw Dog Box	55.99
8	Hammock	10
9	Bottomless Coffee Mugs (4 Pack)	9.99
10	Tea Pot	12.45

The `FETCH` Clause

The FETCH clause is basically Oracle’s equivalent of the LIMIT clause that’s seen in many other SQL databases. It’s implemented specifically as a row limiting clause. It enables us to limit the output of our queries to a specific number or percentage of rows.

Here’s an example that uses the FETCH clause:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
FETCH FIRST 3 ROWS ONLY;

Result:

PRODUCTID	PRODUCTNAME	PRODUCTPRICE
1	Left handed screwdriver	25.99
2	Right handed screwdriver	25.99
3	Long Weight (blue)	14.75

In this case I limited the output to just three rows. I used FETCH FIRST 3 ROWS ONLY to achieve this. I could have used a percentage value (eg FETCH FIRST 30 PERCENT ROWS ONLY) to achieve the same effect.

We can include an OFFSET clause to limit the output to a different set of three rows:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 5 ROWS FETCH FIRST 3 ROWS ONLY;

Result:

PRODUCTID	PRODUCTNAME	PRODUCTPRICE
6	Chainsaw	245
7	Straw Dog Box	55.99
8	Hammock	10

Here, I used an offset of 5, and so this resulted in the first five rows being skipped. I gave the FETCH clause a value of 3, which ensured that only the next three rows were returned following the offset.

The `ROWNUM` Pseudocolumn

Another option is to use the ROWNUM pseudocolumn. The ROWNUM pseudocolumn contains a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second row has 2, and so on.

We can therefore use ROWNUM to limit the number of rows returned by a query, like this:

SELECT
    ProductId,
    ProductName,
    ProductPrice
FROM Products
WHERE ROWNUM <= 3
ORDER BY ProductId;

Result:

ROWNUM	PRODUCTID	PRODUCTNAME	PRODUCTPRICE
1	1	Left handed screwdriver	25.99
2	2	Right handed screwdriver	25.99
3	3	Long Weight (blue)	14.75

If we apply the OFFSET clause to the above example, the offset will be applied within the context of the three rows. In other words, the row count is already determined before the OFFSET clause is applied. Therefore, applying a positive offset value will further reduce the number of rows returned.

Here’s an example of what I mean:

SELECT
    ROWNUM,
    ProductId,
    ProductName,
    ProductPrice
FROM Products
WHERE ROWNUM <= 3
ORDER BY ProductId
OFFSET 1 ROW;

Result:

ROWNUM	PRODUCTID	PRODUCTNAME	PRODUCTPRICE
2	2	Right handed screwdriver	25.99
3	3	Long Weight (blue)	14.75

Therefore, providing an offset that’s the same as, or greater than, the ROWNUM value will result in no rows being returned:

SELECT
    ROWNUM,
    ProductId,
    ProductName,
    ProductPrice
FROM Products
WHERE ROWNUM <= 3
ORDER BY ProductId
OFFSET 3 ROWS;

Result:

no data found

However, we can change this by using a subquery like the following:

SELECT * FROM (
    SELECT
        ProductId,
        ProductName,
        ProductPrice
    FROM Products
    ORDER BY ProductId
    OFFSET 3 ROWS
    )
WHERE ROWNUM <= 3;

Result:

PRODUCTID	PRODUCTNAME	PRODUCTPRICE
4	Long Weight (green)	11.99
5	Sledge Hammer	33.49
6	Chainsaw	245

Using a subquery like this also forces the ROWNUM condition to be applied after the ordering of the rows.

The `ROW_NUMBER()` Analytic Function

Another option we have is to use the ROW_NUMBER() analytic function. ROW_NUMBER() is commonly implemented as a window function in many SQL databases. It allows us to get a row number for our result set.

Here’s an example that uses the ROW_NUMBER() function to limit the rows returned in our query:

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER(ORDER BY ProductId) AS rn,
        ProductId,
        ProductName,
        ProductPrice
    FROM Products
    )
WHERE rn <= 3
ORDER BY ProductId;

Result:

RN	PRODUCTID	PRODUCTNAME	PRODUCTPRICE
1	1	Left handed screwdriver	25.99
2	2	Right handed screwdriver	25.99
3	3	Long Weight (blue)	14.75

Sample Data

The FETCH Clause

The ROWNUM Pseudocolumn

The ROW_NUMBER() Analytic Function

The `FETCH` Clause

The `ROWNUM` Pseudocolumn

The `ROW_NUMBER()` Analytic Function