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.
__________________ All offers from me are valid for 24 Hrs unless otherwise noted.
Using a subquery to determine the max rating for each record...
PHP Code:
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...
