0

I want to calculate the SECOND highest hourly_rate of employee in each project. But I don't know where I am doing wrong

My concerned table structure:

project_team: project_code (FK), employee_id (FK), hourly_rate

this is my query which gives the only one record that too isn't second highest.

NOTE: I have 3 projects and each project has many employees.

SELECT * FROM (
    SELECT * FROM project_team GROUP BY project_code ORDER BY hourly_rate DESC LIMIT 1,1
) e;
Imam Bux
  • 1,006
  • 11
  • 27

1 Answers1

2

joins aren't limited to equality, you can also join on <. This means if we find the max(hourly_rate) we can then join to that result based on all hourly_rate that is less than the max, and that also has the same project_code, from there we just have to group again and get the max value from the joined table. 

select p1.project_code, max(p2.hourly_rate)
from
 (select project_code, max(hourly_rate) r from project_team group by project_code) p1
 left join project_team p2
   on p2.project_code = p1.project_code and p2.hourly_rate < p1.r
group by project_code;

(this assumes there is always a second highest (either all employees have same rate, or only one employee))

select p1.project_code, ifnull(max(p2.hourly_rate), p1.r) 
from
 (select project_code, max(hourly_rate) r from project_team group by project_code) p1
 left join project_team p2
   on p2.project_code = p1.project_code and p2.hourly_rate < p1.r
group by project_code;

And this one will give the highest value, if there is no second highest.

Demo Fiddle

select t.project_code, t.second_max_rate, t2.employee_id
from
  (select p1.project_code, ifnull(max(p2.hourly_rate), p1.r)  second_max_rate
    from
     (select project_code, max(hourly_rate) r from project_team group by project_code) p1
    left join project_team p2
      on p2.project_code = p1.project_code and p2.hourly_rate < p1.r
  group by project_code) t
  inner join project_team t2
    on t.project_code = t2.project_code and t.second_max_rate = t2.hourly_rate
  group by t.project_code;

This version also includes employee - take note however that it abuses mysqls leniancy with respect to group by, you will get AN employee, but there's no guarantee about which one you will get if there are multiple with the same rate.

pala_
  • 8,901
  • 1
  • 15
  • 32