Find the highest salary of instructors in that department-CodePudding

SQL query: For each department, find the highest salary of instructors in that department.

Instructor schema: Instructor(ID , name, dept_name, salary) Id primary key, dept_name foreign key referencing department.

department(dept_name, building, budget)

Instructor table values:

ID       Iname     dept_name    salary
10101   Srinivasan  Comp. Sci.  65000
12121   Wu          Finance     90000
15151   Mozart      Music       40000
22222   Einstein    Physics     95000
32343   El Said     History     60000
33456   Gold        Physics     87000
45565   Katz        Comp. Sci.  75000
58583   Califieri   History     62000
76543   Singh       Finance     80000
76766   Crick       Biology     72000
83821   Brandt      Comp. Sci.  92000
98345   Kim         Elec. Eng.  80000

If we try to find out highest salary manually,

Brandt      Comp. Sci
Wu          Finance
Mozart      Music
Einstein    Physics
Califieri   History
Crick       Biology 
Kim         Elec. Eng.

Now I run this query,

select distinct 
       T.Iname, 
       T.dept_name 
  from instructor as T, 
       instructor as S 
 where T.salary > S.salary 
 group by T.dept_name;

I got

Kim Elec. Eng.
Brandt  Comp. Sci.
Crick   Biology
Singh   Finance
Gold    Physics
Califieri   History

I got incorrect result for Finance and Physics and music is not even included.

I can not find my mistake. I would like to know where I need to modify?

Thank you.

CodePudding user response：

On MySQL 8 , this problem is easy to handle using RANK:

WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) rnk
    FROM department
)

SELECT Iname, dept_name, salary
FROM cte
WHERE rnk = 1;

Note that the above would report two or more instructors within a given department who share the highest salary, should a tie occur.

CodePudding user response：

Using EXISTS

 select T.Iname, 
        T.dept_name 
 from instructor as T
 where not exists (select 1
                   from instructor as S
                   where S.dept_name = T.dept_name
                   and T.salary < S.salary);

CodePudding user response：

Another way is to find biggest salaries for the department as in the sub-query td_1 and then join it with the data table using salary and department

select td.Iname, td.dept_name, td.salary
  from test_data td
  join (select max(salary) salary, dept_name
          from test_data td 
         where td.dept_name = dept_name
         group by dept_name) td_1
    on td_1.dept_name = td.dept_name
   and td_1.salary = td.salary

CodePudding user response：

I think the below SQL will work.

select Iname, dept_name from instructor as t1 where t1.salary=(select max(salary) from instructor as t2 where t1.dept_name = t2.dept_name);

select t1.Iname, t1.dept_name from instructor as t1 left join instructor as t2 on t1.dept_name = t2.dept_name and t1.salary < t2.salary where t2.dept_name is NULL;

CodePudding user response：

Try this:

  SELECT ID, Iname, dept_name, salary 
    FROM instructors
GROUP BY Iname,dept_name 
ORDER BY salary DESC ;