SQL to retrieve the latest records, grouping by unique foreign keys
- by jbox
I'm creating query to retrieve the latest posts in a forum using a SQL DB.
I've got a table called "Post". Each post has a foreign key relation to a "Thread" and a "User" as well as a creation date.
The trick is I don't want to show two posts by the same user or two posts in the same thread. Is it possible to create a query that contains all this logic?
# Grab the last 10 posts.
SELECT id, user_id, thread_id
FROM posts
ORDER BY created_at DESC
LIMIT 10;
# Grab the last 10 posts, max one post per user
SELECT id, user_id, thread_id
FROM post
GROUP BY user_id
ORDER BY date DESC
LIMIT 10;
# Grab the last 10 posts, max one post per user, max one post per thread???
