Get the top 3 commentator from a table in mysql query

Question

I have 3 tables

comments(id,question_id, user_Id) // here the user_id is of the user who has asked the question
questions(id,q_desc, user_id)
users(id, name, rank)

A user can ask question and can comment on the questions.

I need a report in which I want to display every question with at most 3 top ranked user, who have commented on it, but the user who has asked the question should not be in the report for that particular question, but he too has the privilege to comment on his question.

EDited:::

Select * from comments inner join users(comments.user_id=u.id) group by question_id order by user.rank desc

@Nerd-Herd: I didn't add my query then, because I am having the problem in starting this, because the problem lies in the top 3 ranked users for each question — Sashi Kant, Jul 15 '12 at 05:53

Zane Bien · Accepted Answer · 2012-07-15T08:06:52.957

It's messy but it works:

SELECT 
    a.question_id, a.user_id, a.name, a.rank
FROM
(
    SELECT a.*, b.name, b.rank
    FROM
    (
        SELECT DISTINCT b.question_id, b.user_id
        FROM questions a
        INNER JOIN comments b ON a.id = b.question_id AND a.user_id <> b.user_id
    ) a
    INNER JOIN users b ON a.user_id = b.id
) a
INNER JOIN
(
    SELECT a.question_id, b.rank
    FROM
    (
        SELECT DISTINCT b.question_id, b.user_id
        FROM questions a
        INNER JOIN comments b ON a.id = b.question_id AND a.user_id <> b.user_id
    ) a
    INNER JOIN users b ON a.user_id = b.id
) b ON a.question_id = b.question_id AND a.rank <= b.rank
GROUP BY 
    a.question_id, a.user_id, a.name, a.rank
HAVING 
    COUNT(1) <= 3
ORDER BY 
    a.question_id, a.rank DESC

EDIT: This produces the same results and is more succinct:

SELECT a.*
FROM
(
   SELECT DISTINCT a.question_id, a.user_id, b.name, b.rank
   FROM comments a
   INNER JOIN users b ON a.user_id = b.id
) a
INNER JOIN 
    questions b ON a.question_id = b.id AND a.user_id <> b.user_id
INNER JOIN
(
   SELECT DISTINCT a.question_id, a.user_id, b.rank
   FROM comments a
   INNER JOIN users b ON a.user_id = b.id
) c ON b.id = c.question_id AND a.rank <= c.rank
GROUP BY 
    a.question_id, a.user_id, a.name, a.rank
HAVING 
    COUNT(1) <= 3
ORDER BY 
    a.question_id, a.rank DESC;

These solutions also account for users that have posted more than one comment in the same question.

See both solutions in action at SQLFiddle

Excellent Zane, I am accepting your answer, can you please help me in the hierarchy issue http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries — Sashi Kant, Jul 15 '12 at 07:39
@SashiKant, Thanks! By the way, I just made a more succinct version of the solution if you're interested. See revised answer. — Zane Bien, Jul 15 '12 at 07:40

Get the top 3 commentator from a table in mysql query

1 Answers1