How to select rows in MySQL, whose column value is repeated no more than 2 times


There is a table

 1|     1|test1
 2|     1|test2
 3|     2|test3
 4|     3|test4
 5|     3|test5
 6|     1|test6

You need to make a selection so that it contains no more than 2 products from each category. How to do it?

Here is a link to the DB, for checking


I think the easiest way to do this would be using variables:

select *
  from (
        select T.*,@n:=if(@cat=cat_id,@n+1,1) as Num,@cat:=cat_id
          from test7 T,(select @n:=0,@cat:=0) A
         order by cat_id
       ) A
 where Num<=2

Sorting in an internal subquery must necessarily begin with cat_id, then you can add other fields to it to control which of the two records to show.

The "regular SQL" variant:

 select *
   from test7
  where id in(select max(id) from test7 group by cat_id
              select min(id) from test7 group by cat_id

From the limitations of the method: the record with the minimum and maximum ID is always selected from any group.

Scroll to Top