Display Employee first and second maximum salaries?
Ask by hanmandlu vasani
Updated 24 Sep 2020
first maximum
select max(salary) from employee
select top 1 * from employee where salary=(select max(salary) from employee)
order by salary desc
select * from employee e where 0=(select count(distinct salary) from employee where salary>e.salary)
select top 1 * from employee where salary=(select max(salary) from employee)
order by salary desc
select * from employee where salary=(select max(salary) from employee e where Department=e.Department )
SELECT * FROM (
SELECT firstname,salary,Dense_Rank() OVER (ORDER BY salary DESC) as Rnk FROM Employee)
as Result WHERE Result.Rnk =1
second maximum
select max(salary) from employee where salary<(select max(salary) from employee)
select top 1 * from employee where salary<(select max(salary) from employee)
order by salary desc
select * from employee e where 1=(select count(distinct salary) from employee where salary>e.salary)
SELECT * FROM (
SELECT firstname,salary,Dense_Rank() OVER (ORDER BY salary DESC) as Rnk FROM Employee) as
Result WHERE Result.Rnk =2
department wise
select * from employee e where salary=(select max(salary) from employee where Department=e.Department)
order by salary desc
select distinct salary,department from employee e where salary=(select max ( salary) from employee
where Department=e.Department) order by department
select Department,max(salary) from employee group by Department