List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:July 14 2010 2:49pm
Subject:RE: Select w/ group by question
View as plain text  


 


> Date: Wed, 14 Jul 2010 10:25:22 -0400
> Subject: Select w/ group by question
> From: smullen27@stripped
> To: mysql@stripped
> 
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or less just bothering me if there
> is an easy/efficient way to gather the following data from a single query.
> 
> Example Tables
> 
> 
> Products
> Type Cost Vendor_id
> ------------------------------
> -----------
> apple 1 1
> apple 3 2
> apple 7 3
> pear 2 1
> pear 4 2
> pear 2 3
> 
> Vendors
> Vendor_id Vendor_name
> --------------------------------------------
> 1 Walmart
> 2 Target
> 3 Kmart
> 
> 
> I would like to obtain the least cost of each product type and its
> associated vendor.
> 
> So...I would like to see a result similiar to the following:
> 
> Type Cost Vendor_id Vendor_name
> apple 1 1 Walmart
> pear 2 1 Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not
> really concerned with which vendor is chosen in the result set here.)
> 
> 
> If I do: 

select a.type, min(a.cost), a.vendor_id, b.vendor_name from
products a join 

vendors b 

on a.vendor_id = b.vendor_id 

group by a.type,a.vendor_id, b.vendor_name 

 

all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. 

 

If you remove the vendor_id and vendor_name from the group by, 

 

select a.type, min(a.cost), a.vendor_id,b.vendor_name from
products a join vendors b 

on a.vendor_id = b.vendor_id 

group by a.type;

 

you get a single row with the lowest cost for each product, 

but the vendor_id's and vendor_name's are incorrect because you are not grouping by them.
> 
> Is there a way to do this from a single query. I know I can concat things
> together and imbed a select in my where clause to get the result I want, but
> this is horribly inefficient.
> My real tables have somewhere around 30 million rows in them.

MG>put ascending indexes on vendor_id and vendor_name columns

MG>reorg the tables so the rows will be in vendor_name (within vendor_id) order

 

MG>reselect

MG>select a.type, min(a.cost), a.vendor_id,b.vendor_name from
MG>products a join vendors b 

MG>on a.vendor_id = b.vendor_id 

MG>order by a.type;


> 
> Thanks
> 
> Scott

 		 	   		  
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. 
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5
Thread
Select w/ group by questionScott Mullen14 Jul
  • Re: Select w/ group by questionPeter Brawley14 Jul
    • Re: Select w/ group by questionScott Mullen14 Jul
  • Re: Select w/ group by questionMichael Satterwhite14 Jul
  • RE: Select w/ group by questionMartin Gainty14 Jul