OracleDB12c New Feature: New Row Limiting Clause in SELECT

Oracle Database 12c now includes a row_limiting_clause in the SELECT statement. This new clause limits the rows returned in a more meaningful and effective way, compared to the methods available in previous releases [using ROWNUM or using Top-N rows using subqueries or using analytic functions like RANK or DENSE_RANK].

The syntax for the new clause according to the Oracle documentation is:

the row_limiting_clause follows the ORDER BY clause in the SELECT statement, though ORDER BY is not mandatory, but is recommended for consistent results if you are looking for Top-N analysis.

OFFSET clause is used to skip the specified number of rows before the limiting begins. If the offset is higher than the number of rows retrieved or is NULL, no rows are returned. ROW, ROWS keywords are there only for readability.

FETCH clause can specify the number of rows to return or a percentage of rows to return.  FIRST, NEXT keywords can be used interchangeably and is for semantic clarity only.

ONLY specifies to return exact number or percent of rows to return.

WITH TIES specifies to return all rows that have the same sort keys as the last row of the row-limited result set (requires an ORDER BY clause).

Examples:

 

 

 

 

 

 

 

 

One Response to OracleDB12c New Feature: New Row Limiting Clause in SELECT

  1. Wow. What a useful new feature. Your examples are great!

    Thanks,
    Rick

Leave a Reply

%d bloggers like this: