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.
Advertisement
Answer
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