From: Peter Brawley
Date: September 18 2012 11:52am
Subject: Re: getting certain rows from a group by
List-Archive: http://lists.mysql.com/mysql/228179
Message-Id: <5058606B.20907@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2012-09-18 5:53 AM, Larry Martell wrote:
> 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?

Might be, but what's the importance of doing it as one query? I'd start 
by saving this result to a temp table and developing the new query. When 
that's running, see if you can to optimise a query built by replacing 
the reference to the temp table with the original query.

PB

>