Mysql group by join wrong result

Question

I not figure out to evaluate the maximum creation date value in a join. Below the tables envolved:

Supervisor
SupervisorCompany
Company

A Supervisor can be related to many Company, and a Company can be related to many Supervisor. The relation ManyToMany is represented by SupervisorCompany table that conatains the foreign key related.

--------------
| Supervisor |
-----------------------------------------------------------
IdSupervisor  |     Name   | Surname |    CreationTime     |
------------------------------------------------------------
1             | Maximilian | Green   | 2022-01-01 01:00:01 |
------------------------------------------------------------
2             |   Josh     | Nice    | 2023-04-03 01:00:01 | 
------------------------------------------------------------
3             |  Albert    | Cloud   | 2022-03-01 01:32:01 |
------------------------------------------------------------
4             |  Peter     | Dark    | 2022-03-01 01:32:01 |
------------------------------------------------------------

--------------
| Company     |
--------------------------------------
IdCompany     |    Brand   | Address |
--------------------------------------
1             |      X     |         |
--------------------------------------
2             |      Y     |         | 
--------------------------------------
3             |      Z     |         |
--------------------------------------
4             |      J     |         | 
--------------------------------------


-------------------------
| SupervisorCompany     |
--------------------------------------
Id     |Id_Supervisor   | Id_Company |
--------------------------------------
8      |      1         |    1      |
--------------------------------------
9      |      2         |    1      | 
--------------------------------------
10     |      3         |    1      |
--------------------------------------
11     |      4         |    3      | 
--------------------------------------

I want return the newest CreationTime Supervisor of a Company for each Company.

I executed this query:

select *, MAX(Supervisor.CreationTime) from Company 
    inner join SupervisorCompany on Company.IdCompany = SupervisorCompany.IdCompany 
    inner join Supervisor on SupervisorCompany.IdSupervisor = Supervisor.IdSupervisor 
    GROUP BY Company.IdCompany;

But the result is:

| Maximilian | Green   | 2022-01-01 01:00:01 |   1   |

insted

|   Josh     | Nice    | 2023-04-03 01:00:01 |   1   |

I know that there is somethings wrong in the query but I don't know exactly what is the mistake. I tried with a subquery or others approach but I don't figure out. Thanks in advance

score 0 · Answer 1 · answered Oct 26 '22 at 08:42

You should select only the company ID and the max creation time:

SELECT c.IdCompany, MAX(s.CreationTime) AS MaxCreationTime
FROM Company c
INNER JOIN SupervisorCompany sc ON c.IdCompany = sc.IdCompany 
INNER JOIN Supervisor s ON sc.IdSupervisor = s.IdSupervisor 
GROUP BY c.IdCompany;

The rough rule of thumb for an aggregation query is that we can select only columns which appear in the GROUP BY clause or columns which appear inside aggregate functions (such as MAX()).

Mysql group by join wrong result

1 Answers1