how to retrive only second row from table?

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 ?    25 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

More SQL PLSQL Interview Questions

What is difference between ms sql and mysql?

0 Answers  


How do you use a while loop in pl sql?

0 Answers  


What is structural independence and why is it important?

0 Answers  


what is 'trigger' in sql? : Sql dba

0 Answers  


What are different sql data types?

0 Answers  






What is a unique constraint?

0 Answers  


how to delete duplicate rows from a specified table(only single table) how do you know which join is need to be used

11 Answers   UST,


What is a boolean in sql?

0 Answers  


what is definer rights invoke rights?

1 Answers  


Why is sql better than hql?

0 Answers  


How can I change database name in sql?

0 Answers  


What is the use of pl/sql table?

0 Answers  


Categories