CODEWITHSUNDEEP
mysqlsqlselectgroup-bygreatest-n-per-group
Tropos
Tropos

Reputation: 23

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.

Upvotes: 2

Views: 517

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT c.comment_content, c.comment_date, c.comment_author
FROM comments c 
INNER JOIN (SELECT comment_author, MAX(comment_date) AS comment_date
            FROM comments
            GROUP BY comment_author
           ) A ON c.comment_author = A.comment_author AND c.comment_date = A.comment_date
ORDER BY c.comment_date DESC
LIMIT 10;
  1. Fetch records for all comment_author with latest date so check my inner query.
  2. Then Fetch the required data from main table by joining on comment_author and maximum date

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

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

Upvotes: 1

Related Questions