SQL MAX mixes up lines

Question

I have a little table where i like to get the latest content for each groupId.

This is the Table with my data:

This is my try to get it.

SELECT *, MAX(highest_datetime.creattionDate)
FROM highest_datetime
GROUP BY highest_datetime.groupId;

The MAX(highest_datetime.creattionDate) includes the latest content witch is a success.
But the complete rest of the line does not match the MAX(highest_datetime.creattionDate).
The rest of the row got mixed up.

How do i prevent this?

Do i use it wrong?

here is the data for the Database so you can create it yourself:

CREATE TABLE `test`.`highest_datetime` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `groupId` INT NOT NULL,
  `creationDate` DATETIME NOT NULL,
  `Content` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`groupId`)
) ENGINE = InnoDB;




INSERT INTO `test`.`highest_datetime` (`groupId`, `creationDate`, `Content`)
VALUES
(1, '2023-05-02 00:00:00', 'Newest content'),
(1, '2023-01-01 00:00:00', 'Middle Content'),
(2, '2023-03-01 00:00:00', 'Middle Content'),
(2, '2022-11-01 00:00:00', 'Old Content'),
(2, '2023-06-01 00:00:00', 'New Content'),
(1, '2022-11-20 00:00:00', 'Old Content'),
(2, '2024-11-01 00:00:00', 'Future Content');

Your query is invalid and should raise a syntax error. What DBMS are you using? Is this MySQL running in its notorious cheat mode? With MySQL always `SET sql_mode = 'ONLY_FULL_GROUP_BY'` in order to get informed of invalid group by queries. — Thorsten Kettner, Jun 03 '23 at 19:54
Always tag your request with your DBMS when asking SQL questions, please. — Thorsten Kettner, Jun 03 '23 at 20:07
Could you add more information about witch part of the query you expect to raise a syntax error? I Run MySQL Right now i have this mode: @@sql_mode: "NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION" I will try to set it to SET sql_mode = 'ONLY_FULL_GROUP_BY' — Nils, Jun 03 '23 at 20:29
Just read my answer. Click the demo link in my answer to see it for yourself :-) — Thorsten Kettner, Jun 03 '23 at 20:31
If the proper full group by mode is not set, it is likely you are running an old MySQL version. In newer versions the full group by mode is set by default, because so many people wrote queries that seemed okay, but could return undesired results. MySQL 8 is already five years old. I wouldn't run anything older. If you are running an old version, I suggest you upgrade. — Thorsten Kettner, Jun 03 '23 at 20:35

Thorsten Kettner · Accepted Answer · 2023-06-03T20:15:52.440

You are probably running MySQL in its notorious cheat mode, i.e. you haven't SET sql_mode = 'ONLY_FULL_GROUP_BY', and the DBMS allows invalid aggregation queries.

You have:

SELECT *, MAX(highest_datetime.creationDate)
FROM highest_datetime
GROUP BY highest_datetime.groupId;

So you want one result row per groupid, with the maximum creation date. You also want all columns, but you don't tell the DBMS from which row. For example you select the content, but which? The first in alhabet or the last? You don't state this, and so this query is invalid.

This would raise an error in about every DBMS. In MySQL's old cheat mode however, the DBMS silently applies ANY_VALUE on all non-aggregated columns. In other words it picks the content and other columns from arbitrary rows.

What you want instead is to get the rows with the maximum creation dates for their group. One way to do this:

SELECT id, groupid, creationdate, content
FROM highest_datetime
WHERE (groupid, creationdate) IN
(
  SELECT groupid, MAX(creationdate)
  FROM highest_datetime
  GROUP BY groupid
);

Another approach:

SELECT id, groupid, creationdate, content
FROM
(
  SELECT *, MAX(creationdate) OVER (PARTITION BY groupid) as max_grp_date
  FROM highest_datetime
) with_max_date
WHERE creationdate = max_grp_date;

Add an ORDER BY clause to the queries, if you want your result rows in a particular order.

Demo: https://dbfiddle.uk/FzP5oloZ

SQL MAX mixes up lines

1 Answers1