I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as "Last Run", SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Runs, avgbottom as "Avg Bottom", 3*stdbottom as "3 Sig", maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as "% Success", 3*stdbottom/avgbottom as "3Sig/Avg", AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as "LastRun", COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) < 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep;
This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql