How to make a JOIN so that 1 record from another table is attached to the main table, with certain conditions, from the part in the ORDER plan?

Question

MySQL

An example is a table of posts and comments.
You need to display a list of posts + by the most recent comment on the post.
Or a list of posts + by the most rated comment. Posts should not be repeated as a result.
I'd love to be able to represent this with CakePHP find, but a simple query would work too.

Answer 1

In general, everything is much simpler than it seemed ...

SELECT MAX(с.created)
FROM comments c
GROUP BY c.post_id

Well, plus JOIN post.

Answer 2

select * from posts p join comments c on c.post_id = p.id having max(c.date)

Answer 3

SELECT * FROM posts JOIN LEFT comments ON comments.post_id = posts.id GROUP BY posts.id ORDER BY comment.date DESC

Maybe I'm confusing something, I just woke up)

Answer 4

In your case, ROW_NUMBER() and over(ORDER BY xxx) will help you - this will be in a subquery. The main query will print the first rows of the subquery.