how to retrive only second row from table?
Answers were Sorted based on User's Feedback
Answer / naresh raju
select * from emp where rownum<=2
minus
select * from emp where rownum<2
Is This Answer Correct ? | 63 Yes | 12 No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Answer / bindu
select * from (select rownum r1,e.* from emp e)
where r1=2;
Is This Answer Correct ? | 8 Yes | 4 No |
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 |
What is difference between ms sql and mysql?
How do you use a while loop in pl sql?
What is structural independence and why is it important?
what is 'trigger' in sql? : Sql dba
What are different sql data types?
What is a unique constraint?
how to delete duplicate rows from a specified table(only single table) how do you know which join is need to be used
What is a boolean in sql?
what is definer rights invoke rights?
Why is sql better than hql?
How can I change database name in sql?
What is the use of pl/sql table?