> Date: Wed, 14 Jul 2010 10:25:22 -0400
> Subject: Select w/ group by question
> From: smulle...@gmail.com
> To: mysql@lists.mysql.com
>
> 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
