how to retrive only second row from table?

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / ravi148

31 Answers
47057 Views
IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to retrive only second row from table?..

Answer / naresh raju

select * from emp where rownum<=2
minus
select * from emp where rownum<2

Is This Answer Correct ?

63 Yes

12 No

how to retrive only second row from table?..

Answer / hema

Both the above answers give no correct result..

The first says bind variable should be declared for :n,
and second gives no rows selected...

Is This Answer Correct ?

34 Yes

11 No

how to retrive only second row from table?..

Answer / umadevi

SELECT * FROM emp a WHERE 2 = (SELECT COUNT(rowid) FROM
emp b WHERE a.rowid >= b.rowid)

now it will work. instead of :n we have mention row number.

Is This Answer Correct ?

26 Yes

10 No

how to retrive only second row from table?..

Answer / monika

SELECT * FROM(SELECT a.*,row_number() over(ORDER BY NULL)
rn FROM emp a)
WHERE rn=2

Is This Answer Correct ?

18 Yes

6 No

how to retrive only second row from table?..

Answer / tulasi ravi kumar

hi this tulasi .....+91-9951123501
in SQL Server 2000 we write as...
declare CurTemp scroll cursor for
select top 2 * from <table_name>
open CurTemp
fetch last from CurTemp
close CurTemp
deallocate CurTemp;

in ORACLE , we can write as..

select * fom <table_name>
where rowid=(select rowid from <table_name>where rownum<=2
minus
select rowid from <table_name>
where rownum<2;

so try it ...

Is This Answer Correct ?

17 Yes

5 No

how to retrive only second row from table?..

Answer / zeljko

This answer_10 will work only if there is not order by used
on select statement.
I recommend using dense_rank function to get second row out
as in example;

SELECT * FROM (
SELECT id, first_name, salary,
DENSE_RANK() OVER(ORDER BY salary desc) row_order
FROM employee)
WHERE row_order = 2;

Is This Answer Correct ?

11 Yes

2 No

how to retrive only second row from table?..

Answer / sandhya rani

This is perfect answer...
and do not misguide the ppl by simply writing wrong answer.
until and unless u r perfect..pls do not share your answer

select * from emp where rownum<=2
minus
select * from emp where rownum<2

Is This Answer Correct ?

12 Yes

3 No

how to retrive only second row from table?..

Answer / vamsi krishna

this query will give the correct answer.
all the answers which are given above are absolutely wrong.
(take it easy who posted them)
select * from (select rownum as r1,empno,ename,sal,deptno
from emp) where r1=2;
(take default emp table for execution)

Is This Answer Correct ?

12 Yes

8 No

how to retrive only second row from table?..

Answer / bindu

select * from (select rownum r1,e.* from emp e)
where r1=2;

Is This Answer Correct ?

8 Yes

4 No

how to retrive only second row from table?..

Answer / rajasekar.p

select * from (select a.*,rownum aa from orders a )
where aa=2

Please verify the same you will get the correct answer

Regards
P.Rajasekar

Is This Answer Correct ?

5 Yes

3 No

Post New Answer