Oracle Database has a FETCH clause that allows us to limit the number of rows returned by a SELECT statement.
We can use this clause to specify the exact number of rows or the percentage of rows that a query should return. We can also specify an offset for which to start.
Syntax
The syntax goes like this:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]So we have quite a few options with how we construct our FETCH clauses. The following examples demonstrate the various options.
The ROW and ROWS keywords mean the same thing. Both keywords are provided for semantic clarity and can be used interchangeably.
The same applies to the FIRST and NEXT keywords.
Sample Data
Suppose we have a table with 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 |
We can see that the table contains ten rows of data.
Basic Example of the FETCH Clause
Here’s an example of adding the FETCH clause to the above query in order to limit the number of rows returned:
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 results to just the first three rows.
As previously mentioned, ROW and ROWS can be used interchangeably. So I could have used 3 ROW but it makes more semantic sense to use ROWS plural. If I was only returning one row, I could use FIRST 1 ROW, which would make better semantic sense, but I could equally use FIRST 1 ROWS.
The same applies to the FIRST and NEXT keywords. I could replace FIRST with NEXT and get the same result.
So I could get the same result by changing the whole thing to the following:
SELECT
ProductId,
ProductName,
ProductPrice
FROM Products
ORDER BY ProductId
FETCH NEXT 3 ROW ONLY;Result:
| PRODUCTID | PRODUCTNAME | PRODUCTPRICE |
|---|---|---|
| 1 | Left handed screwdriver | 25.99 |
| 2 | Right handed screwdriver | 25.99 |
| 3 | Long Weight (blue) | 14.75 |
Sorting
The above examples are sorted by the ProductId column. Specifying a different order can change the result:
SELECT
ProductId,
ProductName,
ProductPrice
FROM Products
ORDER BY ProductPrice
FETCH FIRST 3 ROWS ONLY;Result:
| PRODUCTID | PRODUCTNAME | PRODUCTPRICE |
|---|---|---|
| 9 | Bottomless Coffee Mugs (4 Pack) | 9.99 |
| 8 | Hammock | 10 |
| 4 | Long Weight (green) | 11.99 |
Here, the FETCH clause itself is no different than in the previous example. But I changed the order of the results by passing a different column name to the ORDER BY clause. This changed the end result. We still got three rows, but it was a different set of rows. This is because the first three rows are different when sorted by the different column.
Ties
In the previous examples I used the ONLY keyword. When we use the ONLY keyword, the query returns the exact number of rows that we specified, even if there are ties. A tie is when two or more rows contain the same value in the column that we sorted the results by.
We can use WITH TIES instead of ONLY to specify that any ties are included in the result, even if it means exceeding the limit count that we specify.
Here’s an example to demonstrate what I mean:
SELECT
ProductId,
ProductName,
ProductPrice
FROM Products
ORDER BY ProductPrice DESC
FETCH FIRST 4 ROWS WITH TIES;Result:
| PRODUCTID | PRODUCTNAME | PRODUCTPRICE |
|---|---|---|
| 6 | Chainsaw | 245 |
| 7 | Straw Dog Box | 55.99 |
| 5 | Sledge Hammer | 33.49 |
| 1 | Left handed screwdriver | 25.99 |
| 2 | Right handed screwdriver | 25.99 |
Five rows are returned instead of the four that we specified. That’s because we got a tie for fourth place.
In this case I sorted by the ProductPrice column and specified a row limit of 4. As it turns out, two rows contain the same value in their ProductPrice column, which means they both tie for fourth place. The WITH TIES keyword resulted in both of them being returned.
Now, here’s what happens when we replace WITH TIES with ONLY:
SELECT
ProductId,
ProductName,
ProductPrice
FROM Products
ORDER BY ProductPrice DESC
FETCH FIRST 4 ROWS ONLY;Result:
| PRODUCTID | PRODUCTNAME | PRODUCTPRICE |
|---|---|---|
| 6 | Chainsaw | 245 |
| 7 | Straw Dog Box | 55.99 |
| 5 | Sledge Hammer | 33.49 |
| 1 | Left handed screwdriver | 25.99 |
This time, only four rows are returned. Only one of the tied rows is returned.
Specifying a Percentage
We can use the PERCENT keyword to specify a percentage of rows to return instead of an absolute number:
SELECT
ProductId,
ProductName,
ProductPrice
FROM Products
ORDER BY ProductId
FETCH FIRST 30 PERCENT 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 specified 30 percent as the number of rows to return. The table contains ten rows, and so 30 percent of ten is 3.
Adding an Offset
We can combine the FETCH clause with the OFFSET clause to specify an offset:
SELECT
ProductId,
ProductName,
ProductPrice
FROM Products
ORDER BY ProductId
OFFSET 1 ROW FETCH NEXT 3 ROWS ONLY;Result:
| PRODUCTID | PRODUCTNAME | PRODUCTPRICE |
|---|---|---|
| 2 | Right handed screwdriver | 25.99 |
| 3 | Long Weight (blue) | 14.75 |
| 4 | Long Weight (green) | 11.99 |
Here, I used the OFFSET clause to offset the rows by 1. This caused the result set to start at the second row.