Find user’s most recent post in each thread

One common problem that my sequence method can help to solve is how to display the most recent entry that a user has made in each thread that the user has contributed to:


SELECT * FROM posts WHERE usedid = $userid AND postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE AND sequence = 1)


Notice that I use the reverse sequence here (t1.postid <= t2.postid) so that the posts are numbered in descending order with the most recent = 1.