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 |
How to recover a dropped index?
Explain parameter file in oracle.
Select the Nth lowest value from a table?
Respected sir, Please send me technical questions related to oracle apps..
create or replace procedure show_tab_rec ( P_tab VARCHAR2 ) IS cmd varchar2(50); begin cmd := 'select * from '|| P_tab; for int in ( execute immediate cmd ) loop dbms_output.put_line ( int.ename||' '||int.deptno); end loop; end; when i m compling this procedure i m getting this error PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following: . ( ) , * @ % & | = - + < / > at in is mod remainder not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ PLZ solve this error give this question answer asap Thanks advance.......
How to create a new table by selecting rows from another table?
Can you create a synonym without having a table?
Difference between the “verify” and “feedback” command?
Whats the benefit of dbms_stats over analyze?
Differentiate between function and procedure in oracle.
Can Multiple instances be run on Single Machine???
How can one skip any number of rows while loading the DB tables with SQL Loader? Similarly how can one skip any column?