CODEWITHSUNDEEP
mysqlleft-join
今際のアリス
今際のアリス

Reputation: 354

MySQL sort by most comments

I am learning how to use JOINS. I am not sure what I am doing wrong. So I want to sort my entries by the most comments. This is how I thought I would do.

    SELECT a.*, COUNT(b.comment) AS comments_count
    FROM entries a
    LEFT JOIN comments b
    ON a.id = b.entry_id
    ORDER BY comments_count DESC
    LIMIT 6

This is obviously the wrong way, any help? I have a table with comments storing which entry the comment should go to with the row name "entry_id". On the entries table I have the entries with id as the one who is paring up with comments.entry_id.

Edit: I also only get out 1 results by doing this query. Not sure why.

Upvotes: 0

Views: 57

Answers (1)

Stephan
Stephan

Reputation: 8090

You need to use a GROUP BY to get the number of comments for each entry and after that you can sort it.

Try this:

SELECT 
    a.*, 
    COUNT(b.comment) AS comments_count
FROM 
    entries a
LEFT JOIN comments b
    ON a.id = b.entry_id
GROUP BY
    a.id
ORDER BY 
    comments_count DESC

NOTE: when using aggregate functions like count without group by it will always retun you one row.

Upvotes: 4

Related Questions