25np$ 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

 

[Jim_]

SELECT * FROM `table_name` WHERE `prod_id` = # ORDER BY `rating` DESC LIMIT 0,1

Why not do that for each prod_id?

 

[Noobie]

try this

SELECT prod_id, MAX(rating) as 'top_rating' FROM table_name GROUP BY prod_id;

 

[Optinom]

JIM: I have around 100 prod_ids. For every homepage load I need to execute 100 queries. Its not a good idea right. Always better to minimise queries.

Noobie: I also need the other parameters. prod_id is in group by so we can get its value. But I need the other fields as well record-id & prod_name. If i try the same query with these fields it doesnt work. anyway, thanks for your effort.

 

[Kate]

Using a subquery to determine the max rating for each record...

SELECT record_id, prod_name, prod_id, rating
FROM your_table
WHERE your_table.rating = (
(

SELECT max( rating ) 
FROM your_table your_table2
WHERE your_table2.prod_id = your_table.prod_id
)
)

For this example I have assumed your table is called 'your_table'. Please note that in the subquery 'your_table' is aliased as 'your_table2'. Just replace 'your_table' in this code with your actual table name and you'll be fine...

Also, please note that you need at least mySQL 4.0 (?) to do subqueries...

 

[Constantin]

Also, please note that you need at least mySQL 4.0 (?) to do subqueries...

At least 4.1

For versions below 4.1 there is a workaround:
http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

 

[Optinom]

Using a subquery to determine the max rating for each record...

SELECT record_id, prod_name, prod_id, rating
FROM your_table
WHERE your_table.rating = (
(

SELECT max( rating ) 
FROM your_table your_table2
WHERE your_table2.prod_id = your_table.prod_id
)
)

For this example I have assumed your table is called 'your_table'. Please note that in the subquery 'your_table' is aliased as 'your_table2'. Just replace 'your_table' in this code with your actual table name and you'll be fine...

Also, please note that you need at least mySQL 4.0 (?) to do subqueries...

Hi dude, thanks. It works fine. 25np$ on the way!

 

[Kate]

Thanks for the NP$, glad it helps :]