Limit query results to two rows per group

I have a query to list all posts:

开发者_C百科SELECT *, DATE(FROM_UNIXTIME(`timestamp`)) `date` 
FROM `posts`
ORDER BY `date` DESC

The query list all rows, I want to only show last 2 post for each date, how can I do this?

This might work, though I couldn't say much about it's performance...

SELECT DATE(FROM_UNIXTIME(MyTimestamp)) AS ForDate, *
FROM   MyPostsTable
WHERE  2 >=
(
    SELECT COUNT(*)
    FROM   MyPostsTable AS lookup
    WHERE  DATE(FROM_UNIXTIME(lookup.MyTimestamp)) = DATE(FROM_UNIXTIME(MyPostsTable.MyTimestamp))
    AND    lookup.MyTimeStamp >= MyPostsTable.MyTimestamp
)

I'm adapting a sql server/oracle solution to your world. Other column would be the ID of the table and then you could use that in a correlated subquery to get the rest of the columns. Let me know if it works for next time I use MySQL

select l.DATE(FROM_UNIXTIME(`timestamp`)), l.`otherColumn`, count(*) as num
from `posts` as l
left outer join fruits as r
    on l.DATE(FROM_UNIXTIME(`timestamp`)) = r.DATE(FROM_UNIXTIME(`timestamp`))        
       and l.`otherColumn` >= r.`otherColumn`
group by l.DATE(FROM_UNIXTIME(`timestamp`)), l.`otherColumn`
having count(*) <= 2