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 ? | 26 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 a relationship and what are they? : Sql dba
what is Difference between Having and Where clause?
What are the different types of a subquery?
i want to display 1 to 10 numbers using one select statement.
18 Answers HCL, Nyros, Oracle,
Where the integrity constrints are stored in Data Dictionary?
we have a package and we have grants to execute that package inside of that we have table, here we don't have privileges to this table? whether this package will execute or not?
What are the types of operators available in sql?
Can we debug stored procedure?
What is exception? What are the types of exceptions?
how will u find statistics of a database objects?
How to get each name only once from an employee table?
difference between table level constraint and column level and advantages of table level constraint