MYSQL - GROUP BY clause at Professional Level

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rahulephp
Forum Commoner
Posts: 28
Joined: Mon Oct 05, 2009 11:05 am

MYSQL - GROUP BY clause at Professional Level

Post by rahulephp »

How to fetch the "name" FROM programname = "Buy" and description which has maximum words using GROUP BY??

I have 6 columns with thousands of product entries:
Table name : eproducts
Columns : product_id, programname, name, description, clean_modelno

Ex. Products Entries:

product_id : 5001
programname : argos
name : Toshiba 32RV753B
description : By Toshiba (100 words)
model_number : 32RV753B
price : 549.99

product_id : 5002
programname : Buy
name : Toshiba 32 Inch Full HD 1080p Freeview HD LCD TV
description : Television picture quality: Freeview HD digital (Approx 110 words)
model_number : 32RV753B
price : 499

product_id : 5003
programname : Amazon
name : Toshiba 32 Inch Widescreen Full HD 1080p LCD TV
desciption : 66cm Visible LCD HD Ready Integrated Digital (Approx 90 words)
model_number : 32RV753B
price : 650

product_id : 5004
programname : Ebey
name : 2RV753B 32Inch HD Ready 1080p LC HD Read
description : Toshiba 32RV753 / 32RV753B 32" REGZA RV Serie (Approx 97 words)
model_number : 32RV753B
price : 550

When I Group by the products using there model numbers, it gave me the first row as output as all these four products has same model number:

Code: Select all

SELECT *,  
								
MAX(price) as max_price,

MIN(price) as min_price,

count(distinct programname) as total_retailers

FROM eproducts as e

WHERE 
GROUP BY model_number

BUT,
I want to get the "name" FROM programname : Buy and description which has maximum words from all four descriptions.

Please suggest me a good and efficient solution.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MYSQL - GROUP BY clause at Professional Level

Post by VladSun »

Play with some of the suggested here: http://www.xaprb.com/blog/2006/12/07/ho ... up-in-sql/
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply