MySQL inner join with MAX(Date) and Grouping

Question

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.

what's your rules to get nonaggregated column, any value chosen by database will do? if this is the case, you may try [ANY_VALUE](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value) — ProDec, Dec 15 '21 at 11:35
@ProGu Thank you I have tried ANY_VALUE but it is a random value that MySQL assigns so it is risky. I need the latest record (DateUpdated) as it will have the latest status — snowflakes74, Dec 15 '21 at 11:38
*I have a MySQL database 8.0 that has a structure like below* Provide this as CREATE TABLE + INSERT INTO, not as a table. *Is there a way I can rewrite it so I can make it MySQL 8.0 friendly.* ROW_NUMBER() in CTE and select only rows with rn=1. — Akina, Dec 15 '21 at 11:44

score 3 · Accepted Answer · answered Dec 15 '21 at 11:46

3

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).

answered Dec 15 '21 at 11:46

Akina

39,301
5
14
25

Thank you it worked perfectly!! Now I need to learn how to use CTE so I can rewrite all the other queries in the system. – snowflakes74 Dec 15 '21 at 12:00
@snowflakes74 you do not necessarily have to use CTE for this, the answers to the duplicate question show you at least half a dozen different solutions, some faster than the CTE one showed by Akina (although, you will find the same solution there as well). – Shadow Dec 15 '21 at 12:46

MySQL inner join with MAX(Date) and Grouping

1 Answers1