fetch first X rows only, new 12c SQL syntax

View All Scripts Login to Run Script

Script Name fetch first X rows only, new 12c SQL syntax
Description With database 12c you can limit your SQL query result sets to a specified number of rows.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Thursday October 15, 2015

Statement 1

Results limited to 4 rows, however in theory its a arbitrary 4 rows as I did not supply an order by clause

select * from hr.employees fetch first 4 rows only

 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000  -  - 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000  - 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000  - 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000  - 102 60

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
100	Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24000	-	-	90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
102	Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
103	Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60

Statement 2

Now with an order by

select * from hr.employees order by first_name fetch first 4 rows only

 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
121 Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200  - 100 50
196 Alana Walsh AWALSH 650.507.9811 24-APR-06 SH_CLERK 3100  - 124 50
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000  - 102 60

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
121	Adam	Fripp	AFRIPP	650.123.2234	10-APR-05	ST_MAN	8200	-	100	50
196	Alana	Walsh	AWALSH	650.507.9811	24-APR-06	SH_CLERK	3100	-	124	50
147	Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	10-MAR-05	SA_MAN	12000	.3	100	80
103	Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60

Statement 3

and to prove the order by is respected here it is ordered by a different column

select * from hr.employees order by last_name fetch first 4 rows only

 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-04 SA_REP 11000 .3 149 80
166 Sundar Ande SANDE 011.44.1346.629268 24-MAR-08 SA_REP 6400 .1 147 80
130 Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800  - 121 50
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800  - 103 60

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
174	Ellen	Abel	EABEL	011.44.1644.429267	11-MAY-04	SA_REP	11000	.3	149	80
166	Sundar	Ande	SANDE	011.44.1346.629268	24-MAR-08	SA_REP	6400	.1	147	80
130	Mozhe	Atkinson	MATKINSO	650.124.6234	30-OCT-05	ST_CLERK	2800	-	121	50
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database