de-bugged de-bugged - 5 months ago 19
MySQL Question

MySQL Where IN, limit output

So I have a query like

SELECT * FROM table WHERE id IN (1,2,3) LIMIT 10


Is it possible to limit the number of output for each of element in array like

SELECT * FROM table WHERE id 1 LIMIT 10
SELECT * FROM table WHERE id 2 LIMIT 10
SELECT * FROM table WHERE id 3 LIMIT 10

Answer

Yes, you can do it using variables:

SELECT *
FROM (
  SELECT *,
         @seq := IF(id = @id, @seq + 1,
                    IF(@id := id, 1, 1)) AS seq 
  FROM table 
  CROSS JOIN (SELECT @seq := 0, @id := 0) AS vars
  WHERE id IN (1,2,3) 
  ORDER BY id) AS t
WHERE t.seq <= 10