mysql limit query rows by item appear?

Question

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.

Albert Stepanyan · Answer 1 · 2016-07-10T19:17:38.143

0

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

edited Jul 10 '16 at 19:17

answered Jul 10 '16 at 19:08

Albert Stepanyan

3
8

score 0 · Accepted Answer · answered Jul 10 '16 at 19:15

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.

mysql limit query rows by item appear?

2 Answers2