Interview Question: Inquire the employee information of the department's highest salary

Difficulty: Medium

Table employee holds all employee data.

Id	Name	Salary	DepartmentID
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1

 
The table department saves all departmental data.

Id	Name
1	IT
2	Sales

 
Write a section of SQL to find out the highest paid employee information in each department. As shown above, it has the highest salary in the Max,sales department is Henry.

Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000

Answer one:

SELECT d.nameas Department, E1. Name as Employee, E1. Salary as Salary from
Employee E1 join Department D
WHERE E1. DepartmentID = D.id and E1. Salary >= (SELECT MAX (Salary) from Employee E2 WHERE E1. DepartmentID = E2. DepartmentID);

Answer two:

Select B.nameas Department,a.nameas employee, A.salaryas Salary from 
employee A, Department B, (select Max (Salary) as Salary, DepartmentID from Employee Group by DepartmentID) C
WHERE A.departmentid = b.id and a.salary = C.salary and A. DepartmentID = C.departmentid

Source: http://www.ss68.net/blog/50621005547582819512.html