Search code examples
mysqlsqlselectgroup-bygreatest-n-per-group

Select 10 latest comments with unique author from MySQL table

I`ve got MySQL table with such columns: comment_id, comment_content, comment_date, comment_author. I need to get 10 latest comments which have unique comment_author. This query: 

SELECT comment_content, comment_date, comment_author
FROM comments   
GROUP BY comment_author
ORDER BY comment_date DESC
LIMIT 10

doing almost what i need, but it takes 1 oldest comment from each unique author, but i need the latest one. Tried to use GROUP BY comments_author HAVING MAX(comment_date) but nothing changed. Thank you.

Solution

  • You can use left join to achieve this 

    select
c1.comment_content,
c1.comment_date,
c1.comment_author
from comments c1
left join comments c2 on c1.comment_author = c2.comment_author
and c1.comment_date < c2.comment_date
where c2.comment_author is null 
order by c1.comment_date desc limit 10