Is it possible to apply limit on group size?

Question

select * from tabName limit 25

by above query I get 25 rows and I want to add a limit in this result for 5 record per group.

Is it is possible?

Downvoter, please throw the OP a bone and let him know why. :) — Jonathan M, Sep 16 '11 at 15:23
This is the infamous (poorly handled in MySQL) n-per-group which is similar to greatest-n-per-group. I have added the greatest-n-per-group tag because those approaches can be used as it is just a refinement of n-per-group. I hope the (newly) associated answers will be of use. — , Sep 16 '11 at 15:45

score 3 · Accepted Answer · edited May 23 '17 at 12:33

See my answer to How to SELECT the newest four items per category? for the standard solution. This question comes up regularly on StackOverflow, and we try to tag it greatest-n-per-group. But in most cases, people are asking about the greatest 1 per group, for which different solutions are used.

Note that most of the solutions for greatest-n-per-group require you to have some idea of the order of rows in each group, so your query can decide which ones belong in your result set and which ones are the "extras" that should be excluded.

For a solution that doesn't care about the order, MySQL doesn't have ANSI SQL:2003 windowing functions, so solving this problem is a little awkward. But you can do it with some user-variable tricks:

SET @g := NULL;
SET @n := 0;

SELECT * FROM (
  SELECT IF(groupCol = @g, @n:=@n+1, @n:=1) AS n, @g:=groupCol AS groupCol, *
  FROM tabName
  -- put ORDER BY here if applicable
) AS t
WHERE n <= 5;

Replace 5 with any other limit you want to put on the rows per group.

Is it possible to apply limit on group size?

1 Answers1