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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
