Tuesday, February 14, 2006

oracle top n query

select * from
emp a
where 3 = (select count(distinct(sal)) from emp b
where b.sal >a.sal)


SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank =2;

No comments: