- Impact
- 885
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
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
