MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table news (id int, title varchar (10));
insert into news values (1, 'title1'),
(2, 'title2'),
(3, 'title3'),
(4, 'title4')


Records: 4  Duplicates: 0  Warnings: 0


create table comments (id int, news_id int, comment varchar (10), rating int);
insert into comments values (1,1,'Comment 1', 5),
(2,2,'Comment 2', 4),
(3,2,'Comment 5', 6),
(4,2,'Comment 4', 6)

Records: 4  Duplicates: 0  Warnings: 0


select * from news;


id
title


1
title1

2
title2

3
title3

4
title4


select * from comments;


id
news_id
comment
rating


1
1
Comment 1
5

2
2
Comment 2
4

3
2
Comment 5
6

4
2
Comment 4
6


select n.id, n.title, cc.comment, cc.rating
from news n INNER JOIN LATERAl (select c.news_id, c.comment, c.rating
from comments c
where n.id=c.news_id
order by c.rating desc, c.id
limit 1) cc on n.id=cc.news_id


id
title
comment
rating


1
title1
Comment 1
5

2
title2
Comment 5
6


select n.id, n.title, cc.comment, cc.rating from news n join (select c1.news_id, c1.rating, min(c1.comment) as comment
from comments c1
left join comments c2 on c1.news_id=c2.news_id and c2.rating>c1.rating
where c2.rating is null
group by c1.news_id, c1.rating) cc on n.id=cc.news_id


id
title
comment
rating


1
title1
Comment 1
5

2
title2
Comment 4
6