Tuesday, May 10, 2011

SQL Server-Find nth highest salary

First Method:
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Second Method:
Select * from ( select rank() over (partition by sal order by sal desc NULLS LAST) rn from tablename)
where rn = &N;
Third Method:
Find 1, 2,3 and nth highest salary
select top 1 salary from (
select distinct top n salary from tab order by salary desc ) a
order by salary asc
Fourth Method(Oracle):
select level, max(‘col_name’) from my_table
where level = ‘&n’
connect by prior (‘col_name’) > ‘col_name’)
group by level;

No comments:

Post a Comment