Page 1 of 1

MYSQL - GROUP BY clause at Professional Level

Posted: Fri Oct 15, 2010 6:13 am
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.

Re: MYSQL - GROUP BY clause at Professional Level

Posted: Fri Oct 15, 2010 7:09 am
by VladSun
Play with some of the suggested here: http://www.xaprb.com/blog/2006/12/07/ho ... up-in-sql/