MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE data1(
  id int auto_increment primary key,
  field1 int,
  field2 varchar(20),
  field3 varchar(20),
  field4 decimal(18,2)
);
INSERT INTO data1(field1, field2, field3, field4)
VALUES
(0,'aaaaaaa', 'ABC', 12.20),
(0,'aaaaaaa', 'DEF', 23.20),
(0,'aaaaaaa', 'GHI', 45.20),
(0,'aaaaaaa', 'JKL', 32.20),
(0,'aaaaaaa', 'MNO', 44.20),
(0,'aaaaaaa', 'PQR', 56.20),
(0,'aaaaaaa', 'STU', 33.20),
(0,'bbbbbbb', 'ABC', 24.20),
(0,'bbbbbbb', 'DEF', 53.20),
(0,'ccccccc', 'ABC', 24.20),
(0,'ccccccc', 'DEF', 64.20),
(0,'ccccccc', 'GHI', 23.20),
(0,'ccccccc', 'JKL', 45.20),
(0,'ccccccc', 'MNO', 65.20);
select *
from
(
  select
  *,
  ROW_NUMBER() OVER(PARTITION BY field2) AS xx
  from data1
) z
where z.xx<4


id
field1
field2
field3
field4
xx


1
0
aaaaaaa
ABC
12.20
1

2
0
aaaaaaa
DEF
23.20
2

3
0
aaaaaaa
GHI
45.20
3

8
0
bbbbbbb
ABC
24.20
1

9
0
bbbbbbb
DEF
53.20
2

10
0
ccccccc
ABC
24.20
1

11
0
ccccccc
DEF
64.20
2

12
0
ccccccc
GHI
23.20
3

id	field2	field3	field4	xx
1	aaaaaaa	ABC	12.20	1
2	aaaaaaa	DEF	23.20	2
3	aaaaaaa	GHI	45.20	3
8	bbbbbbb	ABC	24.20	1
9	bbbbbbb	DEF	53.20	2
10	ccccccc	ABC	24.20	1
11	ccccccc	DEF	64.20	2
12	ccccccc	GHI	23.20	3