Skip to content
Advertisement

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

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

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:

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)

Advertisement

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

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

Results:

post_id comment_id total_like_votes
0 2 7
1 3 10

db<>fiddle here

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement