aabejero aabejero - 4 months ago 5
SQL Question

MySQL query resultset order by most number of result/matches in descending order

I'm working on a function that matches interests of a user and groups. Based on the results of my query, I am able to get the matches however I need help sorting the result in descending order based on the group_id with the most result.

For example, base on the result set below, I want to sort it like this... group_id 47 will be on top because it have 4 results, followed by group_id 44 and group_id 48 with 2 results and those with 1 result like group_id's 40, 42, 43 and 49 will be at the bottom.

Here is the sample query.

SELECT * FROM `group_questionnaire_answers`
WHERE (question_id = 1 AND answer =3)
OR (question_id = 2 AND answer =1)
OR (question_id = 3 AND answer =4)
OR (question_id = 4 AND answer =4)
OR (question_id = 5 AND answer =4)
OR (question_id = 6 AND answer =3)
OR (question_id = 7 AND answer =3)
OR (question_id = 8 AND answer =4)
OR (question_id = 9 AND answer =5)
OR (question_id = 10 AND answer =2)


Here is the sample result set.

enter image description here

I appreciate your help. Thanks.

Answer

It depends on how you want:

SELECT group_id, count(*) AS num
FROM group_questionnaire_answers
WHERE ...
GROUP BY 1 ORDER BY 2 DESC

If you want to show all the details, you need to use self-join then:

SELECT a.*
FROM group_questionnaire_answers AS a
JOIN (
    SELECT group_id, count(*) AS num
    FROM group_questionnaire_answers
    WHERE ...
    GROUP BY 1
) AS b ON a.group_id = b.group_id
WHERE ...
ORDER BY b.num DESC

WHERE ... === your orignal SQL where

Comments