CODEWITHSUNDEEP
mysqlsql
TempUser
TempUser

Reputation:

Question regarding writing SQL query

Consider I have two tables/columns:

Employee - > EmpId, DeptNo, EmpName, Salary
Department -> DeptNo, DeptName

Write a query to get employee names who is having maximum salary in all of the departments. I have tried this:

Select max(salary),empname 
from Employee 
where deptno = (select deptno 
                from department
                where deptname in('isd','it','sales')

Is it correct? Actually it's a interview question.

Upvotes: 1

Views: 176

Answers (2)

Paul McLoughlin
Paul McLoughlin

Reputation: 2293

Personally I'd use a cte and row_number for a question like this. For example:

with myCTE as
(
    select e.empName, e.salary, d.deptName, 
        row_number() over (partition by e.deptNo order by e.salary desc) as rn
    from Employee as e
    inner join Department as d
        on d.DeptNo=e.DeptNo
)

select m.empName, m.deptName, m.salary
from myCTE as m
where m.rn=1

In the case of ties (two employees in the same department have the same max salary) then this is non-deterministic (it will just return one of them). If you want to return both of them then change the row_number to a dense_rank.

Upvotes: 0

mateusz.kijowski
mateusz.kijowski

Reputation: 140

This is an example of groupwise max mysql pattern. One way to do it would be:

    SELECT e.salary, e.name, d.deptname
    FROM Employee AS e 
     JOIN (
       SELECT max(salary) AS max_sal, deptno
       FROM Employee
       GROUP BY deptno
     ) AS d_max ON (e.salary=d_max.max_sal AND e.deptno=d_max.deptno)
     JOIN Department AS d ON (e.deptno = d_max.deptno)

Though it will return more than one row for a department if more than one employee has a maximum salary in a department

Upvotes: 2

Related Questions