Randomise rows for each group and select N rows with 2 different criteria

Question

(SELECT schemename, message FROM RandomMessagesSet where type = 'ES' ORDER BY RAND()) 
 UNION ALL (SELECT schemename, message FROM RandomMessagesSet where type = 'HE' ORDER BY RAND()) ORDER BY schemename; 

This gives the list of all the messages with their scheme names. Is there a way to get 3 each of type "ES" and 2 each of type "HE" for each of schemename?

This is not a homework but part of a research problem that would feed into designing a user study. I tried using LIMIT and JOIN by looking at most of the posts here but still stuck.

Please help me. Your help would assist me to design my second last experiment for my PhD.

EDIT: Thanks to the most empathetic person who downvoted this. You should try doing a PhD yourself to get the feel of it.

I'm afraid I cannot provide sample data due to nature of research work.

Desired output:

Answer 1

In MySQL 8.0 and above, we can use Window Functions. We use a Partition over an expression of concatenated string of schemename and type.

Try (DB Fiddle):

SELECT dt.schemename, 
       dt.type, 
       dt.message
FROM 
(
 SELECT 
   schemename, 
   type, 
   message, 
   ROW_NUMBER() OVER (PARTITION BY CONCAT(schemename, '-', type) 
                      ORDER BY RAND()) AS row_num 
 FROM RandomMessagesSet
) AS dt 
WHERE (dt.type = 'ES' AND dt.row_num <= 3) OR 
      (dt.type = 'HE' AND dt.row_num <= 2) 
ORDER BY dt.schemename