List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 18 2012 10:53am
Subject:getting certain rows from a group by
View as plain text  
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?
Thread
getting certain rows from a group byLarry Martell18 Sep
  • Re: getting certain rows from a group byPeter Brawley18 Sep
    • Re: getting certain rows from a group byLarry Martell18 Sep
      • RE: getting certain rows from a group byRick James18 Sep
        • Re: getting certain rows from a group byLarry Martell18 Sep
          • RE: getting certain rows from a group byRick James18 Sep
            • Re: getting certain rows from a group byLarry Martell18 Sep
              • RE: getting certain rows from a group byRick James18 Sep
                • Re: getting certain rows from a group byLarry Martell19 Sep
                  • Re: getting certain rows from a group byLarry Martell19 Sep
                    • Re: getting certain rows from a group byLarry Martell19 Sep
                      • RE: getting certain rows from a group byRick James19 Sep
                        • Re: getting certain rows from a group byLarry Martell20 Sep
      • Re: getting certain rows from a group byLarry Martell18 Sep
  • Re: getting certain rows from a group byhsv19 Sep
    • Re: getting certain rows from a group byLarry Martell19 Sep
      • Re: getting certain rows from a group byLarry Martell19 Sep
    • Re: getting certain rows from a group byLarry Martell19 Sep
      • Re: getting certain rows from a group byhsv19 Sep