MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE article (
    id INT NOT NULL PRIMARY KEY, 
    author VARCHAR(256),
    title VARCHAR(10),
    rating INT
);
INSERT INTO article (id, author, title, rating) VALUES
(1,  'Adams',  'M', 33),
(2,  'Matt',   'M', 31),
(3,  'Grace',  'F', 25),
(4,  'Harry',  'M', 20),
(5,  'Scott',  'M', 30),
(6,  'Sarah',  'F', 30),
(7,  'Tony',   'M', 30),
(8,  'Lucy',   'F', 27),
(9,  'Zoe',    'F', 30),
(10, 'Megan',  'F', 26);
CREATE TABLE comment_article (
    id INT NOT NULL PRIMARY KEY, 
    author VARCHAR(256),
    code_art INT NOT NULL
);
INSERT INTO comment_article (id, author, code_art) VALUES
(1,  'Adams', 1),
(2,  'Matt',  1),
(3,  'Grace', 1),
(4,  'Harry', 2),
(5,  'Scott', 2),
(6,  'Sarah', 2),
(7,  'Tony',  3),
(8,  'Lucy',  3),
(9,  'Zoe',   3),
(10, 'Megan', 3),
(11, 'Emily', 4),
(12, 'Peter', 4),
(13, 'John',  5),
(14, 'Kate',  5),
(15, 'James', 6),
(16, 'Cole',  6),
(17, 'Dennis',7),
(18, 'Smith', 7),
(19, 'Zack',  7),
(20, 'Jill',  7);
select * from comment_article


id
author
code_art


1
Adams
1

2
Matt
1

3
Grace
1

4
Harry
2

5
Scott
2

6
Sarah
2

7
Tony
3

8
Lucy
3

9
Zoe
3

10
Megan
3

11
Emily
4

12
Peter
4

13
John
5

14
Kate
5

15
James
6

16
Cole
6

17
Dennis
7

18
Smith
7

19
Zack
7

20
Jill
7


SELECT comment_article.*, @rank := CASE
    WHEN @partval = code_art THEN @rank + 1
    WHEN (@partval := code_art) IS NOT NULL THEN 1
END AS rnum
FROM comment_article, (SELECT @rank := NULL, @partval := NULL) AS x
ORDER BY code_art, id;


id
author
code_art
rnum


1
Adams
1
1

2
Matt
1
2

3
Grace
1
3

4
Harry
2
1

5
Scott
2
2

6
Sarah
2
3

7
Tony
3
1

8
Lucy
3
2

9
Zoe
3
3

10
Megan
3
4

11
Emily
4
1

12
Peter
4
2

13
John
5
1

14
Kate
5
2

15
James
6
1

16
Cole
6
2

17
Dennis
7
1

18
Smith
7
2

19
Zack
7
3

20
Jill
7
4


select
*
from article as art 
 left join (
    SELECT comment_article.*, @rank := CASE
             WHEN @partval = code_art THEN @rank + 1
             WHEN (@partval := code_art) IS NOT NULL THEN 1
           END AS rnum
    FROM comment_article, (SELECT @rank := NULL, @partval := NULL) AS x
    ORDER BY code_art, id
) ca on ca.code_art = art.id

where ca.id is null or ca.rnum < 4
order by art.id, ca.id


id
author
title
rating
id
author
code_art
rnum


1
Adams
M
33
1
Adams
1
1

1
Adams
M
33
2
Matt
1
2

1
Adams
M
33
3
Grace
1
3

2
Matt
M
31
4
Harry
2
1

2
Matt
M
31
5
Scott
2
2

2
Matt
M
31
6
Sarah
2
3

3
Grace
F
25
7
Tony
3
1

3
Grace
F
25
8
Lucy
3
2

3
Grace
F
25
9
Zoe
3
3

4
Harry
M
20
11
Emily
4
1

4
Harry
M
20
12
Peter
4
2

5
Scott
M
30
13
John
5
1

5
Scott
M
30
14
Kate
5
2

6
Sarah
F
30
15
James
6
1

6
Sarah
F
30
16
Cole
6
2

7
Tony
M
30
17
Dennis
7
1

7
Tony
M
30
18
Smith
7
2

7
Tony
M
30
19
Zack
7
3

8
Lucy
F
27
null
null
null
null

9
Zoe
F
30
null
null
null
null

10
Megan
F
26
null
null
null
null
id	author	code_art
1	Adams	1
2	Matt	1
3	Grace	1
4	Harry	2
5	Scott	2
6	Sarah	2
7	Tony	3
8	Lucy	3
9	Zoe	3
10	Megan	3
11	Emily	4
12	Peter	4
13	John	5
14	Kate	5
15	James	6
16	Cole	6
17	Dennis	7
18	Smith	7
19	Zack	7
20	Jill	7