List:General Discussion« Previous MessageNext Message »
From:Scott Mullen Date:July 14 2010 8:18pm
Subject:Re: Select w/ group by question
View as plain text  
Peter

Thanks for the link.  I've never run across this page before, but it has
tons of useful information....as well as several answers on how to implement
what I was trying to do.

Ended up going with a solution similar to this example (from the page you
referenced):

SELECT
  item,
  SUBSTR( MIN( CONCAT( LPAD(price,6,0),supplier) ), 7)   AS MinSupplier,
    LEFT( MIN( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MinPrice,
  SUBSTR( MAX( CONCAT( LPAD(price,6,0),supplier) ), 7)   AS MaxSupplier,
    LEFT( MAX( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MaxPrice
FROM  products
GROUP BY item;

Pretty straight forward and does not require another join back to the same
table with 30+ million rows.

Thanks

Scott

On Wed, Jul 14, 2010 at 10:35 AM, Peter Brawley <peter.brawley@stripped
> wrote:

>  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
>>
>>

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