Annie Linnik Annie Linnik - 1 year ago 107
MySQL Question

MySQL -​ SELECT MAX(), and get corresponding fields

​OK, the task is: get the list with ID of every employee and the ID of the last department where he worked. It's becoming more complicated cause one person can work in different departments at one time, so we need to get his last department where he has the max rate.


ID_employee| ID_department | end_date | rate
1 22 2016-01-01 1
2 25 NULL 0.3
2 27 NULL 1
3 22 2013-12-12 0.5
3 22 2014-05-05 0.5

is the last day when employee worked, and NULL value means that his contract is actual today.

The result must look like:

ID_employee | ID_department | end_date | rate
1 22 2016-01-01 1
2 27 NULL 1
3 22 2014-05-05 0.5

I found out how to select max() with corresponding fields by using join:

SELECT table.id_employee, id_department
FROM table
JOIN ( SELECT id_employee,
IF (MAX( end_date IS NULL ) = 1 , "0000-00-00", MAX( end_date )) as max_end_date
FROM table GROUP BY id_employee) maxs ON maxs.id_employee = table.id_employee
WHERE maxs.max_end_date = IFNULL(table.end_date, "0000-00-00")
GROUP BY table.id_employee

However, there are ALL corresponding rows in the result:

ID_employee | ID_department | end_date | rate
1 22 2016-01-01 1
2 25 NULL 0.3
2 27 NULL 1
3 22 2014-05-05 0.5

The question is, how to get NOT JUST corresponding rows to MAX(end_date), but with MAX(rate) too? I assume that HAVING might help, but I still don't know what exactly must be there.

And maybe there are other ways to solve problem with better performance, because this query works about 16s while the table has ~30 000 rows.

Answer Source

Could you try with the query below:

SELECT  T1.ID_employee,  
        CASE WHEN maxs.max_end_date = "0000-00-00" THEN NULL ELSE maxs.max_end_date END AS end_date, 
FROM TestTable T1
JOIN (  SELECT  id_employee,
                MAX(ID_department) AS ID_department,
                IF (MAX( end_date IS NULL ) = 1, "0000-00-00",  MAX( end_date )) AS max_end_date
        FROM TestTable 
        GROUP BY id_employee ) maxs ON maxs.id_employee = T1.id_employee AND maxs.ID_department = T1.ID_department
WHERE maxs.max_end_date = IFNULL(T1.end_date, "0000-00-00")
GROUP BY T1.id_employee

Please find the Live Demo


As per the comments the following query helped to achieve the result:

SET @CurrentDate := CURDATE();

SELECT T2.ID_employee, 
       CASE WHEN MR.Max_end_date = @CurrentDate THEN NULL ELSE T2.end_date END AS end_date, 
       MR.MaxRate AS rate
FROM TestTable T2 
    SELECT T1.ID_employee, MAX(T1.rate) AS MaxRate, MD.Max_end_date
    FROM TestTable T1
    JOIN (
        SELECT  ID_employee,
                MAX(CASE WHEN end_date IS NULL THEN @CurrentDate ELSE end_date END) AS Max_end_date
        FROM TestTable 
        GROUP BY ID_employee
        ) MD ON MD.ID_employee = T1.ID_employee
    WHERE MD.Max_end_date = IFNULL(T1.end_date, @CurrentDate)
    GROUP BY T1.ID_employee
) MR ON MR.ID_employee = T2.ID_employee AND MR.MaxRate = T2.rate 
WHERE MR.Max_end_date = IFNULL(T2.end_date, @CurrentDate)

Working Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download