Select rows between two row numbers from database

You are here : Home / Database Tutorials
In this post we Select rows between one row to another in



1. Select rows between one number to another in MySql >
--Execute these MySql database scripts  >
CREATE TABLE my_schema.employee(ID int,NAME  varchar(255) );
INSERT INTO my_schema.employee(ID, NAME)VALUES(1, 'ankit');
INSERT INTO my_schema.employee(ID, NAME)VALUES(2, 'rohit');
INSERT INTO my_schema.employee(ID, NAME)VALUES(3, 'amy');
INSERT INTO my_schema.employee(ID, NAME)VALUES(4, 'ric');
INSERT INTO my_schema.employee(ID, NAME)VALUES(5, 'hay');
INSERT INTO my_schema.employee(ID, NAME)VALUES(6, 'wat');
INSERT INTO my_schema.employee(ID, NAME)VALUES(7, 'kev');
INSERT INTO my_schema.employee(ID, NAME)VALUES(8, 'stu');
INSERT INTO my_schema.employee(ID, NAME)VALUES(9, 'sac');
INSERT INTO my_schema.employee(ID, NAME)VALUES(10, 'gay');
INSERT INTO my_schema.employee(ID, NAME)VALUES(11, 'lax');
INSERT INTO my_schema.employee(ID, NAME)VALUES(12, 'adam');


EMPLOYEE table will look like this >



Must know : In mySql >
OFFSET - default value of OFFSET is 0.
OFFSET +1 is the number from which MySql database engine will start fetching the rows.
LIMIT - It is number of rows that will be fetched from MySql database.


-- Fetch top 5 rows in MySql database
select * from my_schema.employee limit 5;
select * from my_schema.employee limit 5  OFFSET 0;



-- Fetch rows between 5-10 (including) in MySql database
select * from my_schema.employee limit 6  OFFSET 4;


-- Fetch rows between 6-10 (including) in MySql database
select * from my_schema.employee limit 5  OFFSET 5;





-- Fetch rows between 4-6 (including) in MySql database
select * from my_schema.employee limit 3  OFFSET 3;



-- Fetch rows between 8-11 (including) in MySql database
select * from my_schema.employee limit 4  OFFSET 7;




2. Select rows between one number to another in Oracle >
--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 top 5 rows in Oracle database
select * from EMPLOYEE where ROWNUM< 6;



-- Fetch 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 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 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 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 ;

Labels: Database
eEdit
Must read for you :