Prior to Oracle12c you needed to use an inner query to get the 10 first rows of a table. Example:
Get the 10 first rows ordered by user_id in ascending order:
drop table DEMO1; create table DEMO1 as select substr(username,1,20) username, user_id, from all_users; select * from (select username, user_id from DEMO1 order by user_id asc) where rownum <= 10; USERNAME USER_ID -------------------- ---------- XS$NULL 2147483638 SYSKM 2147483619 SYSDG 2147483618 SYSBACKUP 2147483617 DVSYS 1279990 DBFS_USER 111 SCOTT 109 BI 108 SH 107 IX 106
Now with 12c, this is extremely simple!!!
Oracle 12c provides enhanced support for top-n analysis.
- OFFSET provides a way to skip the N first rows in a result set before starting to return any rows
- The FETCH clause limits the number of rows returned in the result set
- For the result offset clause, the value of the integer literal must be equal to 0 (default if the clause is not given), or positive. If it is larger than the number of rows in the underlying result set, no rows are returned.
- For the fetch first clause, the value of the literal must be 1 or higher. The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.
Here are several examples:
Select the First 3 rows:
select USER_ID, USERNAME from DEMO1 order by USER_ID FETCH FIRST 3 ROWS ONLY;
USER_ID USERNAME
---------- ------------
0 SYS
7 AUDSYS
8 SYSTEM
Select 3 rows after the first 4:
select USER_ID, USERNAME from DEMO1 order by USER_ID OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;
USER_ID USERNAME
---------- ------------
21 GSMADMIN_INTERNAL
22 GSMUSER
23 DIP
Select all the rows after the 5th one
select USER_ID, USERNAME from DEMO1 order by USER_ID OFFSET 5 rows ;
USER_ID USERNAME
---------- --------------------
22 GSMUSER
23 DIP
36 ORACLE_OCM
47 DBSNMP
48 APPQOSSYS
49 XDB
50 ANONYMOUS
60 GSMCATUSER
......
......
Select 31% of the rows:
select USER_ID, USERNAME from DEMO1 order by USER_ID FETCH FIRST 31 PERCENT ROWS ONLY;
USER_ID USERNAME ---------- -------------------- 0 SYS 7 AUDSYS 8 SYSTEM 13 OUTLN 21 GSMADMIN_INTERNAL 22 GSMUSER 23 DIP 36 ORACLE_OCM 47 DBSNMP 48 APPQOSSYS 49 XDB 50 ANONYMOUS 60 GSMCATUSER 61 WMSYS