In this oracle tutorial we will learn how to select rows between one number to another in Oracle. We will write different queries to fetch data between specified rows i.e. select rows between two row number in oracle pl/sql.
--Execute these oracle database scripts >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(1, 'ankit');
insert into EMPLOYEE values(2, 'rohit');
insert into EMPLOYEE values(3, 'amy');
insert into EMPLOYEE values(4, 'ric');
insert into EMPLOYEE values(5, 'hay');
insert into EMPLOYEE values(6, 'wat');
insert into EMPLOYEE values(7, 'kev');
insert into EMPLOYEE values(8, 'stu');
insert into EMPLOYEE values(9, 'sac');
insert into EMPLOYEE values(10, 'gay');
insert into EMPLOYEE values(11, 'lax');
insert into EMPLOYEE values(12, 'adam');
commit;
--If table already exists then execute the DROP command >
drop table EMPLOYEE;
EMPLOYEE table will look like this >
-- Fetch/select top 5 rows in Oracle database
select * from EMPLOYEE where ROWNUM< 6;
-- Fetch/select rows between 5-10 (including) in Oracle database
(select * from EMPLOYEE
where ROWNUM < 11 )
MINUS
(select * from EMPLOYEE
where ROWNUM < 5);
OR,
select emp.id, emp.name from ( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=5 and rn< =10 ;
-- Fetch/select rows between 6-10 (including) in Oracle database
(select * from EMPLOYEE
where ROWNUM < 11 )
MINUS
(select * from EMPLOYEE
where ROWNUM < 6);
OR,
select emp.id, emp.name from ( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=6 and rn< =10 ;
-- Fetch/select rows between 4-6 (including) in Oracle database
(select * from EMPLOYEE
where ROWNUM < 7 )
MINUS
(select * from EMPLOYEE
where ROWNUM < 4);
OR,
select emp.id, emp.name from ( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=4 and rn< =6 ;
-- Fetch/select rows between 8-11 (including) in Oracle database
(select * from EMPLOYEE
where ROWNUM < 12)
MINUS
(select * from EMPLOYEE
where ROWNUM < 8);
OR,
select emp.id, emp.name from ( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=8 and rn< =11 ;
So, in this oracle tutorial we learned how to select rows between one number to another in Oracle. We wrote different queries to fetch data between specified rows i.e. select rows between two row number in oracle pl/sql.
Labels:
Oracle