MYSQL : Retrieve a set of records per category from a table

Forum Moderators: open

Message Too Old, No Replies

MYSQL : Retrieve a set of records per category from a table

heavyDave

8:09 am on Jun 8, 2007 (gmt 0)

The problem: I need to retrieve a set of two most recent entries for each user. I would like to do this with a single query.

I have a MySQL table that looks like this:

 userId Ś postId Ś postDate
 1 1 070101
 2 2 070102
 2 3 070102
 1 4 070102
 1 5 070103
 2 6 070104
 1 7 070104
 2 8 070105
 1 9 070107

I want a result set that looks like this:(Latest two results per user)

 userId Ś postId Ś postDate
 1 9 070107
 1 7 070104
 2 8 070105
 2 6 070104

How can I achieve this in a single MySQL query? Is this possible?

FalseDawn

7:20 pm on Jun 9, 2007 (gmt 0)

It is possible as long as your version of MySQL supports subqueries.

Somthing like
SELECT userid, postid, postdate FROM table T WHERE postdate=(SELECT MAX(postdate) FROM table T2 WHERE T2.userid=T.userid)
UNION
SELECT userid, postid, postdate FROM table T where postdate=(SELECT MAX(postdate) FROM table T2 WHERE T2.userid=T.userid AND T2.postdate<>(SELECT MAX(postdate) FROM table T3 WHERE T3.userid=T1.userid))
ORDER BY userid, postid DESC