Row Limiting Clause: FETCH

In Oracle, the FETCH clause is typically used in combination with the ORDER BY clause to limit the number of rows returned. The basic syntax for using FETCH in Oracle is as follows:

SELECT column1, column2, ... 
FROM table_name 
[WHERE conditions]
[ORDER BY column1, column2, ...]
FETCH {FIRST | NEXT} [n] {ROWS | PERCENT} {ONLY | WITH TIES}
;

Here's a breakdown of the syntax elements:

• SELECT column1, column2, ...: Specify the columns you want to retrieve.
• FROM table_name: Identify the table from which you want to retrieve data.
• [WHERE conditions]: Optional clause to specify conditions for selecting rows
• [ORDER BY column1, column2, ...]: Optional clause to specify the order in which the result set should be sorted.
• FETCH {FIRST | NEXT} [n] {ROWS | PERCENT} {ONLY | WITH TIES}: The FETCH clause is used to limit the number of rows returned.
• {FIRST | NEXT}: Specify whether to fetch the first or next set of rows.
• [n]: An optional numeric value representing the number of rows or percentage of rows to fetch.
• {ROWS | PERCENT}: Specify whether to fetch a specific number of rows or a percentage of rows.
• {ONLY | WITH TIES}: Specify whether to retrieve only the specified number or percentage or rows (ONLY) or include additional rows that are tied in the sorting criteria (WITH TIES).

In the FETCH clause, either the keyword FIRST or NEXT is acceptable, but at least one of them is required. There is no functional difference between FIRST and NEXT; the option is available for grammatical readability only.

If no numeric value is present for n, it defaults to one, which makes sense as omitting it compose an expression, "FETCH FIRST ROW ONLY". Note that either ROW or ROWS is acceptable (one is required). Just like the FIRST/NEXT keyword, there is no functional difference between the two and the option is available for grammatical readability only. For example:

An optional keyword PERCENT is used when n is intended to be interpreted as a percentage rather than a numeric value. When employing PERCENT, the result will always be rounded up to the nearest whole number of rows, and it ensures that partial rows are never returned. For example:

In the example table shown above, we have 828 rows in total. So, 1 percent of the number of rows in the table would be 8.28. However, when fetching 1 percent of the rows, Oracle returns the first 9 rows from the table.

At the end, you must have one of ONLY or WITH TIES. ONLY will return the specified number or percentage of rows and no more. On the other hand, WITH TIES will return all the rows returned by ONLY keyword, but possibly with some additional rows. They behave differently when the rows are sorted and there are tie values. For example:

In the figure shown above, we have two different results depending on which keyword is used. When I used ONLY, the returning set has just 5 rows. However, when I used WITH TIES, Oracle returned additional 3 rows. This is because those 3 rows have the same value with the fifth row.

Note that without ORDER BY, the WITH TIES produces the same result as if we had executed FETCH ... ONLY. It won't produce any syntax error, but just has no effect.

OFFSET

By default, the row limiting clause begins with the first returned row. However, you have the option to specify that the FETCH operation should start from a different position within the result set, at a specific number of rows into the range. Here's an example:

Unlike the first one, in the second example, the FETCH skips the first two rows and starts at the third row. The following lists what happens with various specifications of OFFSET:

• Omitted: Defaults to OFFSET = 0. Range begins with the first row returned by the query.
• OFFSET = a negative number: Treated as OFFSET = 0. Range begins with the first row returned by the query.
• OFFSET = 0: Range begins with the first row returned by the query.
• OFFSET = a positive number that is less than or equals to the total set of available rows: Range begins with OFFSET row.
• OFFSET = a positive number that is greater than the total set of available rows: No rows returned.