MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


create table 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)
);
insert into 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');

Records: 7  Duplicates: 0  Warnings: 0


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

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


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


id
groupid
creationdate
content


1
1
2023-05-02 00:00:00
Newest content

7
2
2024-11-01 00:00:00
Future Content


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;


id
groupid
creationdate
content


1
1
2023-05-02 00:00:00
Newest content

7
2
2024-11-01 00:00:00
Future Content

id	groupid	creationdate	content
1	1	2023-05-02 00:00:00	Newest content
7	2	2024-11-01 00:00:00	Future Content