MySQL inner join with MAX(Date) and Grouping

December 15, 2021

I have a MySQL database 8.0 that has a structure like below:

Table1: Child
Id | Name   | EnrolmentId
1  | Nathan | 12345ABC
2  | James  | 56789BCD

Table2 : Enrolments
Id | EnrolmentId |StartDate |  Status | DateUpdated
1  | 12345ABC    |2021-12-01| PENDING | 2021-06-08T02:13:24
2  | 12345ABC    |2021-12-01| CONFIRM | 2021-12-15T04:56:45
3  | 56789BCD    |2021-12-02| CREATED | 2021-06-09T02:13:24
4  | 56789BCD    |2021-12-02| CONFIRM | 2021-12-16T04:56:45

I want to show only 1 row per enrolment with their latest Enrolment Status.

EnrolmentID | Name   | StartDate |  Status | DateUpDated
12345ABC    | Nathan |2021-12-01 | CONFIRM | 2021-12-15T04:56:45
56789BCD    | James  |2021-12-02 | CONFIRM | 2021-12-16T04:56:45

I am using the query below :

 SELECT e.enrolmentId,c.Name,e.startDate, e.Status,e.DateUpdated
    FROM child c INNER JOIN enrolment e ON c.EnrolmentId = e.enrolmentid
    GROUP BY e.enrolmentid ORDER BY c.Name;

It works fine but because my database is in Azure MySQL 8.0xx and as per other suggestions I have set the server paramaters of sql_mode to full_group_by to off but still randomly I get this error till I restart the MySQL server. Below is the error I get.

"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'mydatbase.c.Name' which is not functionally dependent on columns in GROUP BY clause;this is incompatible with sql_mode=only_full_group_by

Is there a way I can rewrite it so I can make it MySQL 8.0 friendly.

>Solution :

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY EnrolmentId ORDER BY e.DateUpdated DESC) rn
    FROM child c 
    INNER JOIN enrolment e USING (EnrolmentId)
)
SELECT * 
FROM cte
WHERE rn = 1
ORDER BY Name;

Of course, replace asterisks with definite columns lists (ambiguous Id – assign proper aliases).