SQL Plus - HOw to get /select the nth row from the table ?how to select first n rows ,last n rows from a ta

GeekInterview.com > Interview Questions > Oracle > SQL Plus

Go To First | Previous Question | Next Question

SQL Plus | Question 112 of 132

HOw to get /select the nth row from the table ?
how to select first n rows ,last n rows from a table

Total Answers and Comments: 12

Last Update: August 07, 2008 Asked by: muthukumar

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

Sorting Options

Page 1 of 2

« First

Last »

August 17, 2006 01:56:11

mahi328

Member Since: March 2006 Contribution: 7

RE: HOw to get /select the nth row from the table ?

nth salary

select salary

from table_name a

where &n (select count(salary) from table_name b where a.salary< b.salary);

n salaries

select salary

from table_name a

where &n> (select count(salary) from table_name b where a.salary< b.salary);

Is this answer useful? Yes | No

September 07, 2006 04:56:39

tdas2004

Member Since: September 2006 Contribution: 5

RE: HOw to get /select the nth row from the table ?

1. select empno a from (select empno rownum a from emp order by empno) where a &temp;

2.select empno a from (select empno rownum a from emp order by empno) where a < &temp

Is this answer useful? Yes | No

September 11, 2006 08:49:29

kamalnomula

Member Since: August 2006 Contribution: 2

RE: HOw to get /select the nth row from the table ?

select sal from tablename where sal (select sal from tablename n);

Is this answer useful? Yes | No

October 22, 2006 02:14:15

pratyush

RE: HOw to get /select the nth row from the table ?

1- nth row from table

select * from emp where rownum< '&give_nth rows'

2- last nth rows

select *
from emp
where rownum< (select count(empno)
from emp
)
minus
select *
from emp
where rownum< (select count(empno)-'&give_numberof _rows'
from emp
)

Is this answer useful? Yes | No

November 07, 2006 02:24:36

deepak singh

RE: HOw to get /select the nth row from the table ?

select * from aj a where rownum < (select count(rownum) from aj b where a.SCRIP_CD b.scrip_cd if you want to reterive ist row from the tableselect * from aj a where rownum (select count(rownum) from aj b where a.SCRIP_CD > b.scrip_cd)if you want to reterive last row from the table

Is this answer useful? Yes | No

November 10, 2006 04:37:14

Vamseek

Member Since: November 2006 Contribution: 4

RE: HOw to get /select the nth row from the table ?

Use distinct to get accurate results...

nth salary

select salary

from table_name a

where &n (select count(distinct salary) from table_name b where a.salary< b.salary);

n salaries

select salary

from table_name a

where &n> (select count(distinct salary) from table_name b where a.salary< b.salary);

Is this answer useful? Yes | No

December 25, 2006 21:48:49

sudhaker

RE: HOw to get /select the nth row from the table ?

here u go....

select * from emp where rowid in(select max(rowid) from emp where rownum< '&n')

Is this answer useful? Yes | No

March 06, 2007 07:52:31

Nikhil_4_Oracle

RE: HOw to get /select the nth row from the table ?

HI All

Just Try table alias

Select * from(Select rownum r e.* from emp e)emp
Where r 5
/

Bye...

Nikhil

Is this answer useful? Yes | No

July 16, 2008 03:10:29

amitsrivastava115

Member Since: October 2007 Contribution: 3

RE: HOw to get /select the nth row from the table ?how to select first n rows ,last n rows from a table

to get the first n number of rows we can use

select * from emp where rowid in (select rowid from emp
where rownum < &n
minus
select rowid from emp
where rownum < 1);

in the same way to get the last n number of rows i.e last five or last two we can use

select * from emp
where rowid in (select rowid from emp
where rownum < (select count(empno) from emp)
minus
select rowid from emp
where rownum < (select count(empno) - &n from emp));

also to get the last row inserted in the table we can use

select * from emp where rowid in (select max(rowid) from emp);

to get the first row inserted we can use
select * from emp where rowid in (select max(rowid) from emp);

hope that helps

Is this answer useful? Yes | No

July 16, 2008 03:11:36

#10

amitsrivastava115

Member Since: October 2007 Contribution: 3

RE: HOw to get /select the nth row from the table ?how to select first n rows ,last n rows from a table

to get the first row inserted use min(rowid) in place of max(rowid)

Is this answer useful? Yes | No

Page 1 of 2

« First

Last »

Go To Top

User Name		Remember Me?
Password

Question Bank