List:General Discussion« Previous MessageNext Message »
From:Michael Satterwhite Date:July 14 2010 2:47pm
Subject:Re: Select w/ group by question
View as plain text  
On Wednesday, July 14, 2010 09:25:22 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.)
> 
> 

Try this:

   select name, product_type, min(cost) from vendors join products on
       vendors.id = products.vendor_id group by product_type;
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