bitgandtter bitgandtter - 4 months ago 8
MySQL Question

mysql limit query rows by item appear?

I need to create a query that get me only the last 3 posts from each user.

i have this query:

SELECT p.*
FROM post AS p
INNER JOIN customer AS c ON c.id = p.customer
ORDER BY p.created DESC


But that will have me all the posts. I need a set that contain only the lates 3 posts from each user not more.

Answer

The simplest way in MySQL is to use variables:

SELECT p.*
FROM (SELECT p.*,
             (@rn := if(@c = p.customer, @rn + 1,
                        if(@c := p.customer, 1, 1)
                       )
             ) as seqnum
      FROM post p CROSS JOIN
           (SELECT @c := 0, @rn := 1) params
      ORDER BY p.customer, p.created DESC
     ) p
WHERE seqnum <= 3;

Note that you are not using any fields from customer so the JOIN is unnecessary unless you are using it for filtering.

Comments