bitgandtter bitgandtter - 1 year ago 98
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:

FROM post AS p
INNER JOIN customer AS c ON = 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 Source

The simplest way in MySQL is to use variables:

             (@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.