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

Question

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

Answer 1

Try this, see if it helps :

SELECT DISTINCT 
id, user_id, thread_id 
FROM posts 
ORDER BY created_at DESC LIMIT 10;

SELECT DISTINCT
id, user_id, thread_id
FROM post
GROUP BY user_id
ORDER BY date DESC
LIMIT 10;

You can also see a tutorial about this and a discussion about this.

cheers! :)

Answer 2

I haven't tested this, but give this a try:

(
  SELECT p1.id, p1.user_id, p1.thread_id
  FROM post AS p1 LEFT OUTER JOIN post AS p2
    ON (p1.user_id = p2.user_id AND p1.date < p2.date)
  WHERE p2.id IS NULL
  ORDER BY p1.date DESC
  LIMIT 10
)
UNION DISTINCT
(
  SELECT p3.id, p3.user_id, p3.thread_id
  FROM post AS p3 LEFT OUTER JOIN post AS p4
    ON (p3.thread_id = p4.thread_id AND p3.date < p4.date)
  WHERE p4.id IS NULL
  ORDER BY p3.date DESC
  LIMIT 10
)
ORDER BY date DESC
LIMIT 10;

Answer 3

What about this? The first query for per user, the second for per user and per thread.

SELECT id, user_id, thread_id
FROM post p1
WHERE id = (SELECT id 
            FROM post 
            WHERE user_id = p1.user_id 
            ORDER BY date DESC 
            LIMIT 1) 
ORDER BY date DESC 
LIMIT 10;

SELECT id, user_id, thread_id
FROM post p1
WHERE id = (SELECT id 
            FROM post 
            WHERE user_id = p1.user_id 
            ORDER BY date DESC 
            LIMIT 1) 
AND id = (SELECT id 
          FROM post 
          WHERE thread_id = p1.thread_id 
          ORDER BY date DESC 
          LIMIT 1) 
ORDER BY date DESC 
LIMIT 10;