mysql - Select distinct to remove duplicate rows? -

i have forum posts , comments table. i'd sort recent comments:

select distinct(p.id)     ,p.title     ,c.id  posts p     ,comments c  c.post_id = p.id  order c.id desc  limit 50; 

however row every comment. know want loop through recent comments , grab first 50 unique posts. can't translate sql.

here's solution without subqueries:

select p.id, p.title, max(c.id) comment_id posts p join comments c    on c.post_id = p.id group p.id order comment_id desc  limit 50 

this way may bit faster , more scalable despite subquery because can optimize on limit clause:

select p.id, p.title, max(c.id) comment_id posts p join (select distinct c.post_id comments c order c.id desc limit 50) t on t.post_id = p.id join comments c   on c.post_id = p.id group p.id order comment_id desc 

make sure there index on comments(post_id).