0

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?

  • Well, which job_id will you want for employee 3? – Rick James Jul 21 at 4:11
  • What version of MySQL are you using? – sticky bit Jul 21 at 5:34
  • @RickJames I want rows 1, 4 and 6, because these are the latest job_id for each individual employee_id – Max13 Jul 21 at 9:01
  • @stickybit MySQL 5.7 – Max13 Jul 21 at 9:05
  • Then you need a "groupwise-max" solution. See the added tag. – Rick James Jul 21 at 16:12
2

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;
  • Thanks for your reply. That's insane how long the query is with only_full_group_by for a "simple" need as that. How would you write it if, instead of selecting the largest id when ties are found, the line with the latest created_at was selected? – Max13 Jul 21 at 15:28
  • BTW, could you please explain me why my request (in my first post) isn't valid with only_full_group_by? I've read the error, and read the explanation online, I don't understand why a nested query is needed to achieve that – Max13 Jul 21 at 15:30
  • @Max13: I think you may not have fully understood my answer. If the created_at is unique per employee both queries will get the row with the latest created_at per employee. But if the created_at isn't unique per employee you need a second criteria if you don't want to have the ties in the result. Say Bob had a job A on 2019-07-21 with ID 1 and also a job B with ID 2 on the same day, 2019-07-21. If you want both jobs A and B go with the first query, otherwise with the second to only get job B. – sticky bit Jul 21 at 15:35
  • 1
    @Max13: You just can't have a column that isn't in the GROUP BY or an argument to an aggregation function in the list of SELECTed columns, because it leave undefined which value should be take from that column in the group. Without only_full_group_by you my just get a random value each time you execute the query. – sticky bit Jul 21 at 15:38
  • 1
    @Max13: The max(created_at) would be the maximum in the group. But the values of the other columns (the *) could be from any row in the group not necessarily from a row with a maximum created_at. – sticky bit Jul 21 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.