MySQL 8.0

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

select version();

CREATE TABLE messages (`Id` int not null primary key, `Name` varchar(1), `Other_Columns` varchar(8));
	
INSERT INTO messages (`Id`, `Name`, `Other_Columns`) VALUES
	(1, 'A', 'A_data_1'),
	(2, 'A', 'A_data_2'),
	(3, 'A', 'A_data_3'),
	(4, 'B', 'B_data_1'),
	(5, 'B', 'B_data_2'),
	(6, 'C', 'C_data_1')
;

create index i1 on messages(name);


version()


8.0.30



Records: 6  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0


SELECT t.*
FROM messages t
JOIN LATERAL (
  SELECT name, MAX(id) AS id 
  FROM messages t1
  WHERE t.name = t1.name
  GROUP BY name
) trn ON t.name = trn.name AND t.id = trn.id
     


Id
Name
Other_Columns


3
A
A_data_3

5
B
B_data_2

6
C
C_data_1


explain
SELECT t.*
FROM messages t
JOIN LATERAL (
  SELECT name, MAX(id) AS id 
  FROM messages t1
  WHERE t.name = t1.name
  GROUP BY name
) trn ON t.name = trn.name AND t.id = trn.id
     


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
t
null
ALL
PRIMARY,i1
null
null
null
6
100.00
Using where; Rematerialize (<derived2>)

1
PRIMARY
<derived2>
null
ref
<auto_key0>
<auto_key0>
12
fiddle.t.Name,fiddle.t.Id
2
100.00
Using index

2
DEPENDENT DERIVED
t1
null
ref
i1
i1
7
fiddle.t.Name
2
100.00
Using index

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t	null	ALL	PRIMARY,i1	null	null	null	6	100.00	Using where; Rematerialize (<derived2>)
1	PRIMARY	<derived2>	null	ref	<auto_key0>	<auto_key0>	12	fiddle.t.Name,fiddle.t.Id	2	100.00	Using index
2	DEPENDENT DERIVED	t1	null	ref	i1	i1	7	fiddle.t.Name	2	100.00	Using index