In this post we will discuss about a new feature that was introduced in Oracle 12c to limit the rows returned in an easier and effective manner compared to methods used in previous releases by using ROWNUM or using Top-N rows or using analytic functions like RANK or DENSE_RANK. Using this Feature Oracle 12c provides improved support for top-n analysis.
This new Row limiting
Clause in Oracle allows us to select the Top N like queries without much
effort. Let’s take a look into the syntax now.
SELECT * FROM Table_Name
ORDER BY column_name DESC
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount |
percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES }
]
Features
of The New Row limiting Clause In Oracle
Fetch Clause is to limit the number of rows
returned in the result set. Fetch Keyword is followed by FIRST or NEXT which can
be used interchangeably and is for semantic clarity only. Fetch clause can specify
the number of rows or a percentage of rows to return which we will see by using
some examples.
