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
