List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 18 2012 6:05pm
Subject:RE: getting certain rows from a group by
View as plain text  
For single query...

Plan A:
Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in
   WHERE bottom = ( SELECT MIN(bottom) ... )

Plan B:
   FROM ( SELECT @min = MIN(bottom),
                 @max = MAX(bottom), ... )
   JOIN data_cst mn  ON bottom = @min
   JOIN data_cst mx  ON bottom = @max 

Plan C:
Get rid of 3rd party packages that eventually get in your way instead of 'helping'.


> -----Original Message-----
> From: Larry Martell [mailto:larry.martell@stripped]
> Sent: Tuesday, September 18, 2012 4:57 AM
> To: peter.brawley@earthlink.net
> Cc: mysql@stripped
> Subject: Re: getting certain rows from a group by
> 
> On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley
> <peter.brawley@earthlink.net> wrote:
> > 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?
> 
> Because it's part of a django based web app, and the class that this is
> part of only supports having a single query. To use multiple queries
> will require a fairly major rewrite of the server side of that app.
> 
> > 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.
> 
> Thanks, I'll look into this.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

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