MySQL - selection of the last message by each of the last 10 authors

Question

I have a table containing blog entries from different authors. What I would like to do is show the most recent entry of each of the 10 most recent authors.

Each post of the author is simply added to the table in order, which means that there can be a launch of messages by one author. I have a damn time coming with one request to do this.

This gives me the last 10 unique identifiers of the author; can it be used as a subsample to capture the last 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

Answer 2

You need a subquery for the last column for each author and order from postid DESC. Then attach this result to the posts table:

 SELECT B.* FROM ( SELECT * FROM ( SELECT userid,MAX(postid) postid FROM posts GROUP BY userid ) AA ORDER BY postid DESC LIMIT 10 ) A INNER JOIN posts B USING (user_id,post_id); 

Make sure you have this index

 ALTER TABLE posts ADD INDEX user_post_ndx (userid,postid); 

Answer 3

 SELECT userid , MAX(postid) AS lastpostid FROM posts GROUP BY userid ORDER BY lastpostid DESC LIMIT 10