CODEWITHSUNDEEP
mysql
user1817490
user1817490

Reputation: 105

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

Upvotes: 1

Views: 64942

Answers (6)

Vahid Hallaji
Vahid Hallaji

Reputation: 7447

This query gives you a list of departments, with that department's highest paid salary, if exists, or null otherwise. In this case, selecting the employee names do not give you the right name and just return the first employee in the linked department! 

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

See on SQL Fiddle

If you wish a list of departments with the highest salary and employee name:

SELECT 
  d.name, 
  e.name, e.salary
FROM 
  department d 
LEFT OUTER JOIN employee e ON (e.department_id = d.id)
WHERE e.salary IN (
  SELECT MAX(em.salary) FROM employee em 
  WHERE em.department_id = d.id
);

See on SQL Fiddle

Upvotes: 0

silpa
silpa

Reputation: 57

select ename from emp where salary in (select max(salary) from emp group by department);

Upvotes: -1

LaviJ
LaviJ

Reputation: 13

SELECT empname,MAX(salary) FROM employee GROUP BY dep_id

Above query will generate an accurate result.

Upvotes: -1

Pushkar Jethwa
Pushkar Jethwa

Reputation: 43

For SQL Server 2008 Not Best Solution...But Fully Working on HR Database Migrated from Oracle 10G

Screen Shot of Result of Below Code

select e.DEPARTMENT_ID,d.MaxSalary,es.FIRST_NAME,dm.MinSalary,esd.FIRST_NAME
from EMPLOYEES e
 join (select department_id,MAX(salary) MaxSalary from EMPLOYEES group by DEPARTMENT_ID) d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
 join (select first_name,DEPARTMENT_ID from EMPLOYEES ess where SALARY in (select MAX(salary) from EMPLOYEES where DEPARTMENT_ID=ess.DEPARTMENT_ID)) es
on e.DEPARTMENT_ID=es.DEPARTMENT_ID
 join (select department_id,min(salary) MinSalary from EMPLOYEES group by DEPARTMENT_ID) dm
on e.DEPARTMENT_ID=dm.DEPARTMENT_ID
 join (select first_name,DEPARTMENT_ID from EMPLOYEES ess where SALARY in (select min(salary) from EMPLOYEES where DEPARTMENT_ID=ess.DEPARTMENT_ID )) esd
on e.DEPARTMENT_ID=esd.DEPARTMENT_ID
group by e.DEPARTMENT_ID,d.MaxSalary,es.FIRST_NAME,dm.MinSalary,esd.FIRST_NAME

Upvotes: 0

Suman Singh
Suman Singh

Reputation: 1377

Table "emp" has 
id, name,d_id,salary
and Table "department" has 
id, dname

fields.

Below query will output higest salary with department name

    SELECT  E.id,
            E.name,
            D.dname,
            max(E.salary) as higest_salary 
    FROM `emp` as E 
            left join department as D 
    on D.id=E.d_id 
    group by E.d_id

Upvotes: 2

Taryn
Taryn

Reputation: 247670

Without seeing a table structure, I would say that you could probably do this a few different ways.

Using an IN clause:

select e.name e_name,
  d.name d_name,
  e.salary
from employee e
inner join department d
  on e.deptid = d.id
where e.salary in (select max(salary)
                   from employee
                   group by deptid);

Or using a subquery:

select e1.name e_name,
  d.name d_name,
  e1.salary
from employee e1
inner join
(
  select max(salary) salary, deptid
  from employee
  group by deptid
) e2
  on e1.salary = e2.salary
  and e1.deptid = e2.deptid
inner join department d
  on e1.deptid = d.id

See SQL Fiddle with Demo of both

Now, MySQL allows you to apply an aggregate function and not apply a GROUP BY to non-aggregated fields in the select list (this cannot be done in sql server, oracle, etc). So you could use to get the same result:

select e.name e_name,
  d.name d_name,
  max(e.salary) salary
from employee e
inner join department d
  on e.deptid = d.id
group by d.name

See SQL fiddle with Demo

Upvotes: 5

Related Questions