user1817490 user1817490 - 6 months ago 15
SQL Question

select all the employee in all departments which are having highest salary in that department

In one of the interviews one person asked me below question

"Write a query to find out all employee in all departments which are having highest salary in that department with department name,employee name and his salary"

It means that there are 100 records in employee table and 10 records in department table.
So it needs to give me 10 records from query plus if there is no employee in any department it still needs to show that department name.

Thanks

Answer

This simple query give you all departments list and it's employee with max salary if exist or null otherwise:

SELECT 
    department.name, 
    employee.name,
    MAX(employee.salary)
FROM 
    department 
LEFT OUTER JOIN employee ON (employee.department_id = department.id)
GROUP BY department.id