8

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
max_
  • 24,076
  • 39
  • 122
  • 211
Charles Johnson
  • 691
  • 1
  • 7
  • 19

3 Answers3

3
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

Nesim Razon
  • 9,684
  • 3
  • 36
  • 48
  • This is close - it returns the most recent postid for each userid, but the rest of the data in the rows doesn't match. For example, this doesn't work: SELECT userid, title, bodytext, MAX(id) id FROM posts GROUP BY userid ORDER BY id DESC LIMIT 10 – Charles Johnson Apr 16 '12 at 21:36
  • right If you want all fields, unfortunately you need a subquery. I edited my answer. – Nesim Razon Apr 16 '12 at 21:43
  • Great, glad I helped. Accepting correct answer will be greater :) – Nesim Razon Apr 16 '12 at 21:51
1

You need a subquery for the last postid of every author and order by postid DESC. Then, join that 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);
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • You shouldn't need the compound index on userID, postID as the postID would be the unique identifier anyhow and would only exist for the one author doing the post. – DRapp Apr 16 '12 at 21:35
  • @DRapp That compound index is needed to speed up the subquery AA (full index scan vs full table scan) – RolandoMySQLDBA Apr 16 '12 at 22:13
1
SELECT userid
     , MAX(postid) AS lastpostid
FROM posts
GROUP BY userid
ORDER BY lastpostid DESC 
LIMIT 10
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235