Oracle implemented ANSI standards for data paging since 12c release. The following illustrates the syntax of the row limiting clause:
[ OFFSET offset ROWS]
FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]
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:
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.
The following statement returns the top 10 transactions with the highest quantities:
SELECT * FROM transaction
ORDER BY quantity desc
FETCH NEXT 10 ROWS ONLY;
For example, a pagination query looks as below:
SELECT * FROM transactions
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
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;
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.
© 2023 Digcode.com