List:General Discussion« Previous MessageNext Message »
From:Scott Mullen Date:July 14 2010 2:25pm
Subject:Select w/ group by question
View as plain text  
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

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