Sql

[bkiran]

Hi,
I'll be happy if anybody can tell me a query which retrieves the second highest salary and the top five salaries from EMP table.

Tx

Kiran

[ales]

Second highest:

select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=2;

Top five:

select sal from (select distinct sal from emp order by sal desc) where rownum<=5;

[Kumar_RP]

Hi,
Use Rank function..

SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST;

Hope this will help u.

Regards

[ksridhar]

SELECT empno, sal
FROM (SELECT empno, sal
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <=n (if n=5)

EMPNO SAL
---------- ----------
7839 5000
7788 3000
7902 3000
7566 2975
7698 2850

if n=5 gives the Top 5 salaries just change the Rownum n u get Top n Values

Thanks
Sridhar

[bkiran]

Hi,
many tx for the queries. but an error is being generated, at the 'order by' clause.
will be happy enough for the right query.

Kiran

[ksridhar]

it should not throw any error in the Order by clause!! I have tested the query again..

Thanks
Sridhar