Select 10 latest comments with unique author from MySQL table

Question

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.

maybe this might help you? : http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by — Krish, Dec 19 '14 at 11:01
@jQuery.PHP.JAVA.net We have very different definitions of perfection — Strawberry, Dec 19 '14 at 11:04

score 1 · Accepted Answer · answered Dec 19 '14 at 11:03

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

Saharsh Shah · Answer 2 · 2014-12-19T11:32:01.937

1

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;

Fetch records for all comment_author with latest date so check my inner query.
Then Fetch the required data from main table by joining on comment_author and maximum date

edited Dec 19 '14 at 11:32

answered Dec 19 '14 at 11:26

Saharsh Shah

28,687
8
48
83

Can you please specify what your inner & overall query does ? Its usful for upcoming visitors. – Pratik Joshi Dec 19 '14 at 11:29

Select 10 latest comments with unique author from MySQL table

2 Answers2