MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE post
    (`id` int, `title` varchar(6))
;
    
INSERT INTO post
    (`id`, `title`)
VALUES
    (0, 'titleA'),
    (1, 'titleB'),
    (2, 'titleC')
;


CREATE TABLE comment
    (`id` int, `id_post` int, `text` varchar(40))
;
    
INSERT INTO comment
    (`id`, `id_post`, `text`)
VALUES
    (1, 0, 'blaa'),
    (2, 0, 'blbb'),
    (3, 1, 'aaaa'),
    (4, 1, 'bbbb') 
;



CREATE TABLE comment_likes
    (`id` int auto_increment primary key, `id_comment` int, `vote` int)
;
    
INSERT INTO comment_likes
    (`id_comment`, `vote`)
VALUES
SELECT * FROM post;
SELECT * FROM comment;
SELECT * FROM comment_likes


id
title


0
titleA

1
titleB

2
titleC




id
id_post
text


1
0
blaa

2
0
blbb

3
1
aaaa

4
1
bbbb




id
id_comment
vote


1
1
1

2
1
1

3
1
1

4
1
1

5
1
1

6
1
-1

7
1
1

8
2
7

9
3
5

10
3
5

11
4
1

12
4
1

13
4
1


WITH cte AS (
   SELECT p.id AS post_id
          , c.id AS comment_id
          , l.like_votes
          , ROW_NUMBER() OVER(
              PARTITION BY p.id
              ORDER BY l.like_votes DESC
          ) AS votes_rank
   FROM   post p 
             LEFT JOIN comment c ON c.id_post = p.id
             LEFT JOIN (
                 SELECT id_comment, SUM(vote) AS like_votes
                 FROM   comment_likes
                 GROUP BY id_comment
             )
             l ON l.id_comment = c.id
)
SELECT * 
FROM   cte
WHERE  votes_rank = 1
AND    like_votes IS NOT NULL
ORDER BY post_id, like_votes




post_id
comment_id
like_votes
votes_rank


0
2
7
1

1
3
10
1