You want to find top n rows per group. This answer provides a generic solution using example data that is different from OP.
In MySQL 8 or later you can use the ROW_NUMBER, RANK or DENSE_RANK function depending on the exact definition of top 5. Below are the numbers generated by these functions based on value sorted descending. Notice how ties are handled:
| pkid | catid | value | row_number | rank | dense_rank |
|---|---|---|---|---|---|
| 1 | p01 | 100 | *1 | *1 | *1 |
| 2 | p01 | 90 | *2 | *2 | *2 |
| 3 | p01 | 90 | *3 | *2 | *2 |
| 4 | p01 | 80 | *4 | *4 | *3 |
| 5 | p01 | 80 | *5 | *4 | *3 |
| 6 | p01 | 80 | 6 | *4 | *3 |
| 7 | p01 | 70 | 7 | 7 | *4 |
| 8 | p01 | 60 | 8 | 8 | *5 |
| 9 | p01 | 50 | 9 | 9 | 6 |
| 10 | p01 | 40 | 10 | 10 | 7 |
Once you have chosen the function, use it like so:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS n
FROM t
) AS x
WHERE n <= 5
DB<>Fiddle
In MySQL 5.x you can use poor man’s rank over partition to achieve desired result: outer join the table with itself and for each row, count the number of rows before it (e.g. the before row could be the one with higher value).
The following will produce results similar to RANK function:
SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid
Make the following change to produce results similar to DENSE_RANK function:
COUNT(DISTINCT b.value)
Or make the following change to produce results similar to ROW_NUMBER function:
ON b.catid = t.catid AND (b.value > t.value OR b.value = t.value AND b.pkid < t.pkid)
DB<>Fiddle