NP$25 for a Simple SQL Query

[Optinom]

I have a MySQL table with following structure:

record_id - prod_name - prod_id - rating

record_id is the only unique field. I can have multiple ratings for the same prod_id.

I need one query (may be nested) that retreives all the fields of the record that has highest rating for each prod_id.



For example if i have the following in the table:

record_id - prod_name - prod_id - rating
1 aaa 10 6
2 bbb 11 7
3 aaa 10 9
4 bbb 11 5
5 ccc 9 3


The result of query should be
3 aaa 10 9
2 bbb 11 7
5 ccc 9 3