June 14, 2011

Sql query to get second maximum salary of employee

Sql query to get second maximum salary of employee

1st Solution:-

select min(sal) from emp e where 1 = (select count(*) from emp where e.sal<sal)

 In this query duplicate values are not considered hence we may not get 3rd maximum salary because there are two employees with the same salary. to avoid the duplication strictly the query needs some changes

2nd Solution :-


select min(sal) from emp e where 1 = (select count(distinct sal) from emp where e.sal<sal)

 The following query displays the total repeating salaries . to get the complete details of the records just replace "sal" with "*" in the following query

3rd Solution:-
select sal from emp e where 1 = (select count(distinct sal) from emp where e.sal<sal)

This is example of select query to get top 3 maximum from table.

select * from (select salaries from SalaryTrans order by salaries desc) where rownum <= 3;
or
select salary from (select distinct salary from SalaryTrans order by salary desc) where rownum<=3