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 <[email protected]
> 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
>>
>>
