How to use LEFT JOIN and limit result to one row based on a count

Let’s say i have 3 tables:

Table ‘post’

id	title
0	titleA
1	titleB
2	titleC

Table ‘comment’

id	id_post	text
0	1	blaa
1	3	blbb
2	5	blcc

Table ‘like’

id	id_comment	vote
0	1	+1
1	5	-1
2	5	+1

I need to get a list of post with the most liked comment.

The query sould sum the vote column (+1, -1…) of the table “like” (not the real name) for each comment per post, keep the higest and add it to the corresponding post.

For example:

post_id	post_title	comment_id	comment_text	like_vote
0	titleA	12	blaaaa	51
1	titleB	25	blabbb	98
2	titleC	63	blaccc	14

$statement = $this->pdo->prepare('SELECT SQL_NO_CACHE p.id AS post_id, p.title AS post_title, c.id AS comment_id, c.text AS comment_text, IFNULL(like.like_vote, 0) AS like_vote FROM post p
    LEFT JOIN  (SELECT * FROM comment) c ON p.id = c.id_post
    LEFT JOIN  (SELECT id_comment, IFNULL(SUM(vote), 0) AS like_vote, FROM like GROUP BY id_comment ORDER BY like_vote DESC) like ON c.id = like.id_comment 
    ORDER BY p.id ASC
    ');

The result i get is, when a post has 2 comment, i get 2 post with each comment and the count like:

post_id: 0, comment_id: 1, like_vote: 5
post_id: 0, comment_id: 2, like_vote: 7
post_id: 1, comment_id: 3, like_vote: 10
post_id: 1, comment_id: 4, like_vote: 3
...

Each comment get the correct sum of like but i don’t know how to keep only the highest comment and avoid multiple row of post with the same id, like this:

post_id: 0, comment_id: 2, like_vote: 7
post_id: 1, comment_id: 3, like_vote: 10

If you can help my to build the query it would be very nice, i just don’t find it…

Edit: (the name of the table like is just a example to keep things clear)

Answer

One way is using a CTE. Use ROW_NUMBER() OVER(…) to sort and rank results by post and largest number of votes. Then grab the largest one, i.e. where votes_rank = 1

post_id	comment_id	like_votes	votes_rank
0	1	5	1
0	2	7	2
1	4	3	1
1	3	10	2

SQL (MySQL 8.x)

Note – using table name comment_likes to avoid having to escape keyword like

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

Results:

post_id	comment_id	like_votes	votes_rank
0	2	7	1
1	3	10	1

db<>fiddle here

---

Update 2022-04-01

MySQL 5.x doesn’t support window functions so you’ll have to resort to subquery’s. Something like this should produce a similar result

 SELECT   p.id AS post_id
          , ( SELECT  c.id AS comment_id
               FROM   comment c INNER JOIN comment_likes l ON l.id_comment = c.id
               WHERE  c.id_post = p.id
               GROUP BY c.id
               ORDER BY SUM(vote) DESC
               LIMIT 1
           ) AS comment_id
           , ( SELECT SUM(l.vote) 
               FROM   comment c INNER JOIN comment_likes l ON l.id_comment = c.id
               WHERE  p.id = c.id_post
               GROUP BY c.id
               ORDER BY SUM(l.vote) DESC
               LIMIT 1
           ) AS total_like_votes
 FROM   post p 
 HAVING total_like_votes IS NOT NULL

Results:

post_id	comment_id	total_like_votes
0	2	7
1	3	10

db<>fiddle here