MySQL Simple pivot table with MAX(), GROUP BY and only_full_group_by

greatest-n-per-groupgroup byMySQL

I'm having trouble using GROUP BY with only_full_group_by.

Here is my employee_job table:

+----+-------------+--------+------------+
| id | employee_id | job_id | created_at |
+----+-------------+--------+------------+
|  1 |           2 |     10 | 2019-01-01 |
|  2 |           3 |     20 | 2019-01-01 |
|  3 |           3 |     21 | 2019-02-01 |
|  4 |           3 |     22 | 2019-03-01 |
|  5 |           4 |     30 | 2019-01-01 |
|  6 |           4 |     35 | 2019-02-01 |
+----+-------------+--------+------------+

I would like to select only the latest lines per employee_id, which I think gives me:

SELECT *, MAX(created_at)
FROM `employee_job`
GROUP BY employee_id;

The thing is, with only_full_group_by, which I can't disable, I get an error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.employee_job.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Well… I tried to read about that, and it seems I don't get the error. Of course, if I add other fields to the GROUP BY, the result still contains multiple times the same id.

Can someone explain to me how to group my results, maybe if GROUP BY isn't the best way to do it, what is, please?

Best Answer

If you want ties, i.e. when an employee has two jobs on the same day, that happen to be the latest one display both, you can join a derived table with query doing the aggregation.

SELECT ej1.*
       FROM employee_job ej1
            INNER JOIN (SELECT ej2.employee_id,
                               max(ej2.created_at) created_at
                               FROM employee_job ej2
                               GROUP BY ej2.employee_id) ej2
                       ON ej2.employee_id = ej1.employee_id
                          AND ej2.created_at = ej1.created_at;

If you want to eliminate ties and say you want the one with the largest ID in such a case, add another level.

SELECT ej1.*
       FROM employee_job ej1
            INNER JOIN (SELECT max(ej2.id) id
                               FROM employee_job ej2
                                    INNER JOIN (SELECT ej3.employee_id,
                                                       max(ej3.created_at) created_at
                                                       FROM employee_job ej3
                                                       GROUP BY ej3.employee_id) ej3
                                               ON ej3.employee_id = ej2.employee_id
                                                  AND ej3.created_at = ej2.created_at
                               GROUP BY ej2.employee_id,
                                        ej2.created_at) ej2
                       ON ej2.id = ej1.id;
Related Question