Larry Martell getting certain rows from a group by
Sep 18, 2012; 06:53
Larry Martell
getting certain rows from a group by
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
Sep 18
Peter Brawley Re: getting certain rows from a group by
Sep 18, 2012; 06:52
Peter Brawley
Re: getting certain rows from a group by
Sep 18
Larry Martell Re: getting certain rows from a group by
Sep 18, 2012; 07:56
Larry Martell
Re: getting certain rows from a group by
Sep 18
Rick James RE: getting certain rows from a group by
Sep 18, 2012; 11:05
Rick James
RE: getting certain rows from a group by
Sep 18
Larry Martell Re: getting certain rows from a group by
Sep 18, 2012; 15:52
Larry Martell
Re: getting certain rows from a group by
Sep 18
Larry Martell Re: getting certain rows from a group by
Sep 18, 2012; 15:53
Larry Martell
Re: getting certain rows from a group by
Sep 18
Rick James RE: getting certain rows from a group by
Sep 18, 2012; 13:01
Rick James
RE: getting certain rows from a group by
Sep 18
Larry Martell Re: getting certain rows from a group by
Sep 18, 2012; 17:56
Larry Martell
Re: getting certain rows from a group by
Sep 18
Rick James RE: getting certain rows from a group by
Sep 18, 2012; 16:41
Rick James
RE: getting certain rows from a group by
Sep 19
hsv Re: getting certain rows from a group by
Sep 19, 2012; 00:04
hsv
Re: getting certain rows from a group by
Sep 19
Larry Martell Re: getting certain rows from a group by
Sep 19, 2012; 10:07
Larry Martell
Re: getting certain rows from a group by
Sep 19
Larry Martell Re: getting certain rows from a group by
Sep 19, 2012; 10:26
Larry Martell
Re: getting certain rows from a group by
Sep 19
Larry Martell Re: getting certain rows from a group by
Sep 19, 2012; 14:36
Larry Martell
Re: getting certain rows from a group by
Sep 19
Larry Martell Re: getting certain rows from a group by
Sep 19, 2012; 14:56
Larry Martell
Re: getting certain rows from a group by
Sep 19
Larry Martell Re: getting certain rows from a group by
Sep 19, 2012; 15:27
Larry Martell
Re: getting certain rows from a group by
Sep 19
Larry Martell Re: getting certain rows from a group by
Sep 19, 2012; 18:21
Larry Martell
Re: getting certain rows from a group by
Sep 19
Rick James RE: getting certain rows from a group by
Sep 19, 2012; 15:56
Rick James
RE: getting certain rows from a group by
Sep 19
hsv Re: getting certain rows from a group by
Sep 19, 2012; 16:55
hsv
Re: getting certain rows from a group by
Sep 20
Larry Martell Re: getting certain rows from a group by
Sep 20, 2012; 08:07
Larry Martell
Re: getting certain rows from a group by
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!