Oracle Database 12c Tutorial: How to get the top N in Oracle Database 12c?
ID: 2013081301
In the past, user has to use ROWNUM to achieve the top N result retrieval and the result pagination. Oracle Database 12c introduces the new SQL syntax to simplify the retrieval of top N result from an ordered set. The syntax is shown as follows:
OFFSET <offset> [ROW | ROWS] FETCH [FIRST | NEXT] [<rowcount> | <percent> PERCENT] [ROW | ROWS] [ONLY | WITH TIES]
Internally, Oracle Database will calculate the row number for the query result and return back the rows based on this configuration.
- You can specify the number of rows or use the percentage like "fetch first 20 percent rows".
- The WITH TIES option will list rows with the same value as ties without increasing the row number.
SQL> select table_name from user_tables order by table_name fetch first 5 rows only; TABLE_NAME -------------------------------------------------------------------------------- ACCESS$ ACLMV$ ACLMV$_REFLOG ACLMVREFSTAT$ ACLMVSUBTBL$
Then, we can setup the offset and retrieve the rows after as follows:
SQL> select table_name from user_tables order by table_name offset 2 rows fetch first 5 rows only; TABLE_NAME ------------------------------------------------------------------------ ACLMV$_REFLOG ACLMVREFSTAT$ ACLMVSUBTBL$ ADMINAUTH$ ALERT_QT
You can see if you increase the offset and keep fetching the top N rows, you can get the query result pagination.
Last Updated: 8/21/2013. Send Comments.