SQL to get latest records, grouping by unique foreign keys
I am creating a query to fetch recent forum posts using SQL DB.
I have a table called Mail. Each message has a foreign key relationship to "Thread" and "User", as well as a creation date.
The trick is that I don't want to show two posts of 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???
+2
a source to share
3 answers
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 on this and a discussion about it.
Hooray!:)
0
a source to share
I haven't tested this, but I'll 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;
0
a source to share
How about this? The first request is for each user, the second for each user and for the stream.
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;
0
a source to share