Query to list members by "likes received" total for a single thread?

[CTXMedia]

Would one of you SQL whizzes mind giving me a query that will display the number of likes each member has received from within a specific thread ID - so it displays something like this:

Member C - 1478
Member T - 1293
Member G - 754
Member A - 15

Many thanks,
Shaun

 

[Chris D]

Try this:

SELECT p.username, COUNT(*) AS like_count
FROM xf_liked_content AS lc
INNER JOIN xf_post AS p ON
    (lc.content_type = 'post' AND lc.content_id = p.post_id)
WHERE p.message_state = 'visible'
    AND p.thread_id = 9999
GROUP BY lc.content_user_id
ORDER BY like_count DESC

Change thread_id = 9999 to whichever thread ID you desire.

 

[CTXMedia]

Try this:

SELECT p.username, COUNT(*) AS like_count
FROM xf_liked_content AS lc
INNER JOIN xf_post AS p ON
    (lc.content_type = 'post' AND lc.content_id = p.post_id)
WHERE p.message_state = 'visible'
    AND p.thread_id = 9999
GROUP BY lc.content_user_id
ORDER BY like_count DESC

Change thread_id = 9999 to whichever thread ID you desire.

Fantastic - many thanks Chris - spot on!

Cheers,
Shaun