Bhuvnesh Gupta Bhuvnesh Gupta - 3 months ago 6
MySQL Question

order by first then group by in mysql

I have 3 tables as follows

questions (q_id,question,cat_id)

answers (a_id,q_id,answer,user_id,rate)

users (user_id,username)

Now I want all questions of a particular category and its one answer if exits which has highest rate and username of that person who has answered.

I am using following query to do this but it is not giving me that answer which has highest rate.

select c.*,d.username,d.user_id
from users d
join (
SELECT b.*,a.question
FROM `questions` a
left join answers b
on a.q_id=b.q_id
WHERE a.`cat_id` = 8
group by b.q_id) c
on c.user_id=d.user_id


What if try this:

SELECT c.*,d.username,d.user_id
FROM users d
    SELECT b.*,a.question
    FROM `questions` a
        SELECT t1.*
        FROM answers t1
        JOIN (
            SELECT q_id, max(rate) AS rate FROM answers GROUP BY q_id
        ) t2
        ON t1.q_id = t2.q_id AND t1.rate = t2.rate
    ) b ON a.q_id=b.q_id
    WHERE a.`cat_id` = 8
    GROUP BY b.q_id) c ON c.user_id=d.user_id

Cause you have not provided some sample data and desired result, so this may not work as you expected.