Home > DeveloperSection > Interviews > Display Employee first and second maximum salaries?

Posted on    April-21-2014 6:50 AM

Ratings:
1 Answer(s)
1588  View(s)
Rate this:

hanmandlu vasani

Total Post:30

Points:150
Posted on    April-22-2014 12:00 AM

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

Don't want to miss updates? Please click the below button!

Follow MindStick