Top-N queries in oracle 12c

Oracle 12c has a new feature to select top N queries easily. This can be done by using the FETCH clause and the syntax is as follows.

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Lets create a table with 100 rows and see how this command works. We will insert a sequence of 1-100 into the table from dual using the connect by clause.

SQL> create table my_tab as
  2  select level id from dual
  3  connect by level <= 100;

Table created.


SQL> select count(*) from my_tab;

COUNT(*)
----------
 100

Now lets check how this feature now. We will sort the ids in descending order and pick the top 5/6 values from the list.

SQL> select * from my_tab
  2  order by id desc
  3  fetch first 5 rows only;

    ID
----------
    100
    99
    98
    97
    96

SQL> select * from my_tab
  2  order by id desc
  3  fetch first 6 percent rows only;

    ID
----------
    100
    99
    98
    97
    96
    95

6 rows selected.

SQL> select * from my_tab
  2  order by id desc
  3  offset 5 rows fetch next 5 percent rows only;

    ID
----------
    95
    94
    93
    92
    91
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s