From: Peter Brawley
Date: July 14 2010 2:35pm
Subject: Re: Select w/ group by question
List-Archive: http://lists.mysql.com/mysql/222204
Message-Id: <4C3DCB40.9020706@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

  Scott,

>I would like to obtain the least cost of each product type and its
>associated vendor.

See "Within-group aggregates" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

-----

On 7/14/2010 9:25 AM, Scott Mullen wrote:
> 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, 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.
>
> Thanks
>
> Scott
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 06:36:00
>