mysql select with a LIMIT by field value

Question

Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?

I tried group by, but then I'll only get 1 per account_id, I want 10 per account_id. Here's what I'm looking for.

SELECT id,account_id FROM whatever {LIMIT to 10 of each account_id}

Who marked this as duplacate and provided that link? Don't you see that is absolutely different issue? — Green, Sep 20 '17 at 05:12

score 0 · Answer 1 · answered Nov 09 '12 at 18:02

You can use rank for each account_id, id group

Example:

SELECT id,account_id FROM (
SELECT id,account_id,
   @acrank:=CASE 
               WHEN @aid <> id THEN 1 
               ELSE @acrank+1 END AS rn,
   @id:=id AS id_set
FROM
  (SELECT @acrank:= -1) nr,
  (SELECT @id:= -1) n,
  (SELECT * FROM Test ORDER BY id, account_id) t
  ) x WHERE rn < 11

mysql select with a LIMIT by field value

1 Answers1