Sunday, 7 February 2016

Query to display 5th to 7th rows from a table

Requirement: Write a query to display 5th to 7th rows from a table.

Solution: For solution of the above requirement, let us create a table FRIENDS, and insert few rows in FRIENDS table as shown below:

create table friends (friend_id number(2),friend_name varchar2(20));

insert into friends values(1, 'Somya');
insert into friends values(2, 'Girish');
insert into friends values(3, 'Rupam');
insert into friends values(4, 'Sharique');
insert into friends values(5, 'Diplu');
insert into friends values(6, 'Moumita');
insert into friends values(7, 'Debabrta');
insert into friends values(8, 'Ajay');

Thus we have the below records in the FRIENDS table

Now, to display 5th to 7th rows from the FRIENDS table, first we will retrieve all the rows with rownum<=7 , and then minus all the rows with rownum<5. Thus the complete query is shown below:

SELECT *
FROM friends
WHERE rowid IN
  (SELECT rowid FROM friends WHERE rownum<=7
  MINUS
  SELECT rowid FROM friends WHERE rownum<5
  );


Thus, the solution to our requirement.

If you like the post please comment, share, and like me on Facebook.


Thanks & Regards,
Susanto Paul