MySQL - Selecting the most recent post by each of the 10 most recent authors

Question

I have a table containing blog posts by many different authors. What I'd like to do is show the most recent post by each of the 10 most recent authors.

Each author's posts are simply added to the table in order, which means there could be runs of posts by a single author. I'm having a heck of time coming up with a single query to do this.

This gives me the last 10 unique author IDs; can it be used as a sub-select to grab the most recent post by each author?

SELECT DISTINCT userid
FROM posts
ORDER BY postid DESC 
LIMIT 10

Answer 1

select userid,postid, win from posts where postid in (
SELECT max(postid) as postid
FROM posts 
GROUP BY userid
) 
ORDER BY postid desc 
limit 10

http://sqlfiddle.com/#!2/09e25/1