Firebird 3.0

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

CREATE TABLE data1(
  field1 int,
  field2 varchar(20),
  field3 varchar(20),
  field4 decimal(18,2)
);
EXECUTE BLOCK AS BEGIN
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'ABC', 12.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'DEF', 23.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'GHI', 45.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'JKL', 32.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'MNO', 44.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'PQR', 56.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'aaaaaaa', 'STU', 33.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'bbbbbbb', 'ABC', 24.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'bbbbbbb', 'DEF', 53.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'ccccccc', 'ABC', 24.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'ccccccc', 'DEF', 64.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'ccccccc', 'GHI', 23.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'ccccccc', 'JKL', 45.20);
INSERT INTO data1(field1, field2, field3, field4) VALUES(0,'ccccccc', 'MNO', 65.20);
END
14 rows affected

select *
from (
  select
  z.*,
  ROW_NUMBER() OVER (partition by z.field2 order by field4 desc) AS xx
from data1 z
)
where xx<4


FIELD1
FIELD2
FIELD3
FIELD4
XX


0
aaaaaaa
PQR
56.20
1

0
aaaaaaa
GHI
45.20
2

0
aaaaaaa
MNO
44.20
3

0
bbbbbbb
DEF
53.20
1

0
bbbbbbb
ABC
24.20
2

0
ccccccc
MNO
65.20
1

0
ccccccc
DEF
64.20
2

0
ccccccc
JKL
45.20
3

FIELD2	FIELD3	FIELD4	XX
aaaaaaa	PQR	56.20	1
aaaaaaa	GHI	45.20	2
aaaaaaa	MNO	44.20	3
bbbbbbb	DEF	53.20	1
bbbbbbb	ABC	24.20	2
ccccccc	MNO	65.20	1
ccccccc	DEF	64.20	2
ccccccc	JKL	45.20	3