SQL to retrieve the latest records, grouping by unique foreign keys

Posted by jbox on Stack Overflow See other posts from Stack Overflow or by jbox
Published on 2010-05-03T04:07:18Z Indexed on 2010/05/03 4:18 UTC
Read the original article Hit count: 406

Filed under:
|
|
|

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???

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql