Group and subquery issue

Question

This is my sample data

CREATE TABLE customer1
(
  rating int(9),
  genre varchar(100),
  title varchar(100)
  );
    
  INSERT INTO customer1 (rating, genre, title)
  VALUES
(2, 'A', 'abc'),
(4, 'A', 'abc1'),
(2, 'B', 'abc2'),
(3, 'B', 'abc3'),
(2, 'C', 'abc4'),
(5, 'C', 'abc5');

I need to find the title with max rating in each genre.

Thanks for the help.

Answer 1

One option uses a subquery for filtering:

select c.*
from customer1
where c.rating = (select max(c1.rating) from customer1 c1 where c1.genre = c.genre)

This would take advantage of an index on (genre, rating).

In MySQL 8.0, you can also use window functions:

select *
from (
    select c.*,
        rank() over(partition by genre order by rating desc) rn
    from customer1 c
) c
where rn = 1