How to retrieve 5th highest sal from emp table?
Answers were Sorted based on User's Feedback
Answer / amit bhagat
select min(a.sal) from (select distinct(d.sal) from emp
order by d.sal desc) a where rownum<=5
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / venkata
hi, earlier query return sal in asending order and when
duplicate salries are there the earlier highest salary
position returns no rows. I hope this is better query.
select * from (select * from emp order by sal desc)
group by
rownum,empno,ename,job,mgr,hiredate,sal,comm,deptno having
rownum=&n
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / aryasen vaikom
SELECT DISTINCT (a.sal)
FROM EMP A
WHERE 5= ( SELECT COUNT (DISTINCT (b.sal))
FROM EMP B
WHERE a.sal<=b.sal )
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / venubabu
Select level,Max(sal) from emp
Where level=&level connect by prior sal>sal
group by level;
sql>Enter value for level:5
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / arvind patil
select rownum,name,salary
from (select name,salary
from employee
order by salary desc )
where rownum=5;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / lingareddy
select top 1 salary from (select distinct top 5 salary from emp order by salary desc ) a order by salary asc
if u have any doubts in SQL kindly reach with me,
Thanks & Regards,
Lingareddy.S
sabbasani.reddy1@gmail.com
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / jithendranath.g
select z2.sal from emp z1, emp z2
where z2.sal <=z1.sal
group by z2.sal
having count(z2.sal)=&n
Is This Answer Correct ? | 0 Yes | 1 No |
Explain the use of record length option in exp command.
how many columns can a plsql table have
How to list all indexes in your schema?
What is a partition in oracle?
I have query like this. select dept_id, max_mark from stude_dept where min_mark= (select min(mini_mark) from stud_dept); How can i optimize this query. Can anyone help me with it
what is difference between foreign key and reference key
How to drop a tablespace?
does the query needs a hint to access a materialized view?
How to start a new transaction in oracle?
How to convert characters to times in oracle?
How to define Data Block size ?
What is the difference between $oracle_base and $oracle_home?