Query to get the question with most answers-CodePudding

There are 2 tables Questions & Answers Each question will have multiple answers

I want to write a query such that "List all the questions and ordered by maximum number of answers"

So the question with max answer appear first, followed by 2nd question with most answer

I am writing like this

select questions.id from questions inner join answers
on questions.id = answers.question_id
order by max(comments.id)

CodePudding user response：

You need to extend your query by grouping the answers by question and order by the answer count descending. Here you go:

select questions.id, count(*) as AnswerCount 
from questions 
inner join answers on questions.id = answers.question_id
group by questions.id
order by count(*) desc

CodePudding user response：

Use the GROUP BY keyword:

In this solution we join the two tables and group the answers by the question_id, after that you order it by the CountOfAnswers.

SELECT COUNT(question_id) as CountOfAnswers, questions.id as QuestionId
FROM answers JOIN questions 
WHERE answers.question_id == questions.id 
GROUP BY question_id 
ORDER BY CountOfAnswers DESC

This solution is the same as above, except for you get only the question_id as result and not the count of the answers for each question.

SELECT questions.id as QuestionId 
FROM answers JOIN questions 
WHERE answers.question_id == questions.id 
GROUP BY question_id 
ORDER BY COUNT(question_id) DESC