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

Posted on    April-21-2014 6:50 AM

 MSSQL Server MSSQL Server 
Ratings:
 1 Answer(s)
  1408  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