MySQL 8.0

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

CREATE TABLE `selected_movies`(
            genres TEXT,
            title TEXT,
            movie_year INT,
            rating DOUBLE
        ) ;
        
INSERT INTO `selected_movies` (`genres`,`title`,`movie_year`,`rating`) VALUES 
('Crime|Drama','Brother (Brat) (1997)','1997',5),
('Animation|Children','Winnie Pooh (1969)','1969',3),
('Drama|Romance','Sandpiper, The (1965)','1965',2),
('Drama|Romance','Cruel Romance, A (Zhestokij Romans) (1984)','1984',5),
('Comedy|Drama|Romance','Down Argentine Way (1940)','1940',3),
('Comedy','Siam Sunset (1999)','1999',3),
('Comedy|Romance','Spellbound (2011)','2011',2),
('Documentary|Drama','On the Ropes (1999)','1999',4),
('Comedy|Documentary','Animals are Beautiful People (1974)','1974',5),
('Comedy','Calcium Kid, The (2004)','2004',4);

SELECT * FROM selected_movies;


genres
title
movie_year
rating


Crime|Drama
Brother (Brat) (1997)
1997
5

Animation|Children
Winnie Pooh (1969)
1969
3

Drama|Romance
Sandpiper, The (1965)
1965
2

Drama|Romance
Cruel Romance, A (Zhestokij Romans) (1984)
1984
5

Comedy|Drama|Romance
Down Argentine Way (1940)
1940
3

Comedy
Siam Sunset (1999)
1999
3

Comedy|Romance
Spellbound (2011)
2011
2

Documentary|Drama
On the Ropes (1999)
1999
4

Comedy|Documentary
Animals are Beautiful People (1974)
1974
5

Comedy
Calcium Kid, The (2004)
2004
4


SET @criteria := 'Drama|Romance';
WITH 
cte1 AS ( SELECT *
          FROM JSON_TABLE(CONCAT('["', REPLACE(@criteria, '|', '","'), '"]'),
                          '$[*]' COLUMNS (criteria VARCHAR(255) PATH '$')) jsontable ),
cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cte1.criteria 
                                       ORDER BY selected_movies.rating DESC, RAND()) rn 
          FROM cte1
          JOIN selected_movies ON LOCATE(cte1.criteria, selected_movies.genres) )
SELECT criteria, title, movie_year, rating
FROM cte2
WHERE rn <= 2;


criteria
title
movie_year
rating


Drama
Brother (Brat) (1997)
1997
5

Drama
Cruel Romance, A (Zhestokij Romans) (1984)
1984
5

Romance
Cruel Romance, A (Zhestokij Romans) (1984)
1984
5

Romance
Down Argentine Way (1940)
1940
3

genres	title	movie_year	rating
Crime\|Drama	Brother (Brat) (1997)	1997	5
Animation\|Children	Winnie Pooh (1969)	1969	3
Drama\|Romance	Sandpiper, The (1965)	1965	2
Drama\|Romance	Cruel Romance, A (Zhestokij Romans) (1984)	1984	5
Comedy\|Drama\|Romance	Down Argentine Way (1940)	1940	3
Comedy	Siam Sunset (1999)	1999	3
Comedy\|Romance	Spellbound (2011)	2011	2
Documentary\|Drama	On the Ropes (1999)	1999	4
Comedy\|Documentary	Animals are Beautiful People (1974)	1974	5
Comedy	Calcium Kid, The (2004)	2004	4

criteria	title	movie_year	rating
Drama	Brother (Brat) (1997)	1997	5
Drama	Cruel Romance, A (Zhestokij Romans) (1984)	1984	5
Romance	Cruel Romance, A (Zhestokij Romans) (1984)	1984	5
Romance	Down Argentine Way (1940)	1940	3