25np$ for a simple SQL query

Smooth · 04-05-2006, 01:02 PM

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_ · 04-05-2006, 01:15 PM

Code:

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

Why not do that for each prod_id?

Noobie · 04-05-2006, 01:26 PM

try this

Code:

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

Smooth · 04-05-2006, 01:43 PM

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.

sdsinc · 04-05-2006, 02:02 PM

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...
????: NamePros.com http://www.namepros.com/programming/184138-25np-for-a-simple-sql-query.html

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

Constantin · 04-05-2006, 11:02 PM

Originally Posted by sdsinc

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:
????: NamePros.com http://www.namepros.com/showthread.php?t=184138
http://dev.mysql.com/doc/refman/4.1/...ubqueries.html

Smooth · 04-06-2006, 12:09 PM

Originally Posted by sdsinc

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 = (

(

????: NamePros.com http://www.namepros.com/showthread.php?t=184138
 
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!

sdsinc · 04-06-2006, 12:48 PM

Thanks for the NP$, glad it helps

04-05-2006, 01:02 PM	THREAD STARTER #1 (permalink)
Smooth ccTLD Specialist Join Date: Jan 2006 Posts: 802	25np$ for a simple SQL query 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 __________________ All offers from me are valid for 24 Hrs unless otherwise noted.

04-05-2006, 01:15 PM	#2 (permalink)
Jim_ NamePros Regular Join Date: Aug 2005 Location: NY, USA Posts: 608	Code: SELECT * FROM `table_name` WHERE `prod_id` = # ORDER BY `rating` DESC LIMIT 0,1 Why not do that for each prod_id? __________________ ask me about the internet

04-05-2006, 01:26 PM	#3 (permalink)
Noobie NamePros Regular Join Date: Feb 2006 Location: Montreal, Quebec, Canada Posts: 324	try this Code: SELECT prod_id, MAX(rating) as 'top_rating' FROM table_name GROUP BY prod_id; __________________ Goldkey.com is a scam What's your BMI? \| Timestamp Generator

04-05-2006, 01:43 PM	THREAD STARTER #4 (permalink)
Smooth ccTLD Specialist Join Date: Jan 2006 Posts: 802	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.

04-05-2006, 02:02 PM	#5 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	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... ????: NamePros.com http://www.namepros.com/programming/184138-25np-for-a-simple-sql-query.html Also, please note that you need at least mySQL 4.0 (?) to do subqueries... __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

04-06-2006, 12:48 PM	#8 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	Thanks for the NP$, glad it helps __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)