How can I stop count after 5 rows in a group?

Question

I am trying to only generate row numbers for five rows of a given group_id:

group_id | row_counter | other_columns
---------+-------------+--------------
111      | 1           | ...
111      | 2           | ...
111      | 3           | ...
111      | 4           | ...
111      | 5           | ...
111      | 6           | ...
111      | 6           | ...
111      | 6           | ...
111      | 6           | ...
222      | 1           | ...
222      | 2           | ...
222      | 3           | ...
222      | 4           | ...
333      | 1           | ...
333      | 2           | ...

I can't figure out how to reset the counter when the group changes:

  SELECT @group_number := `group_id`,
         CASE
           WHEN (`group_id` = @group_number AND @row_number < 6)
             THEN @row_number := @row_number + 1
           WHEN (`group_id` = @group_number AND @row_number = 6)
             THEN @row_number := 6
           ELSE @row_number := 1
         END AS `row_number`
  FROM   (SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 111 AS `group_id`
          UNION ALL 
          SELECT 222 AS `group_id`
          UNION ALL 
          SELECT 222 AS `group_id`
          UNION ALL 
          SELECT 222 AS `group_id`
          UNION ALL 
          SELECT 222 AS `group_id`
          UNION ALL 
          SELECT 333 AS `group_id`
          UNION ALL 
          SELECT 333 AS `group_id`) `m`
         CROSS JOIN (SELECT @row_number := 0, @group_number := 0) AS `n`

Produces

group_id	row_number
111	1
111	2
111	3
111	4
111	5
111	6
111	6
111	6
111	6
222	6
222	6
222	6
222	6
333	6
333	6

What about wrap it all and use `case when rn>6 then 6 else rn end`? — markalex, Apr 12 '23 at 19:34
This would be **easy** in a modern DB (which 5.7 is _NOT_) using window functions. Even 8.0 can do it. — Joel Coehoorn, Apr 12 '23 at 19:44
https://stackoverflow.com/questions/1895110/row-number-in-mysql — jarlh, Apr 12 '23 at 19:45

Ergest Basha · Accepted Answer · 2023-04-12T20:06:55.270

Below you will find the same answer from Mark Byers , but with a little change based on your requirements.

SELECT group_id ,
       rn
FROM ( SELECT group_id,
              @rn := IF(@prev = group_id, @rn + 1, 1) AS rn,
              @prev := group_id
       FROM (   SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 333 AS `group_id`
                UNION ALL 
                SELECT 333 AS `group_id`
                ) `m`
       JOIN (SELECT @prev := NULL, @rn := 0) AS vars
       ORDER BY group_id
     ) AS T1

You should consider upgrade to 8.x

https://dbfiddle.uk/EFS4fit3

Another simplified example

SELECT t.*, 
       @rownum := @rownum + 1 AS row_number
  FROM (   SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 111 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 222 AS `group_id`
                UNION ALL 
                SELECT 333 AS `group_id`
                UNION ALL 
                SELECT 333 AS `group_id`
                ) t, 
       (SELECT @rownum := 0) r

https://dbfiddle.uk/-N9a2mIb

group_id	row_number
111	1
111	2
111	3
111	4
111	5
111	6
111	6
111	6
111	6
222	6
222	6
222	6
222	6
333	6
333	6

group_id	row_number
111	1
111	2
111	3
111	4
111	5
111	6
111	6
111	6
111	6
222	6
222	6
222	6
222	6
333	6
333	6

How can I stop count after 5 rows in a group?

1 Answers1

group_id	row_number
111	1
111	2
111	3
111	4
111	5
111	6
111	6
111	6
111	6
222	6
222	6
222	6
222	6
333	6
333	6