CODEWITHSUNDEEP
mysqlleft-join
Bert
Bert

Reputation: 855

SQL: select items with number of comments AND latest comment date

I want to display the items with the number of comments and the date of the last comment, each item. 

SELECT item.*, 
  count(comments.itemid) AS commentcount, 
  comments.created AS commentcreated 
FROM table_items AS item 
LEFT JOIN table_comments AS comments ON (comments.itemid = item.id) 
WHERE item.published=1 
GROUP BY item.id 
ORDER BY item.created DESC

Now the commentcreated value is the date of the first comment: I want the last comment. Order by doesn't work on the LEFT JOIN. How can this be accomplished?

Upvotes: 0

Views: 261

Answers (1)

Jaydee
Jaydee

Reputation: 4158

Try

SELECT item.*, 
  count(comments.itemid) AS commentcount, 
  max(comments.created) AS commentcreated 
FROM table_items AS item 
LEFT JOIN table_comments AS comments ON (comments.itemid = item.id) 
WHERE item.published=1 
GROUP BY item.id 
ORDER BY item.created DESC

You need to tell MySQL which of the dates in the group to use with max(), min() or one of the other aggregating functions.

Upvotes: 2

Related Questions