On Tue, Sep 18, 2012 at 2:05 PM, Rick James <rja...@yahoo-inc.com> wrote: > 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
Can you elaborate on this? I don't see how this will give me what I need. I'm not looking for the min or max bottom - I already have that - I'm looking for the row from each group that has the min and max bottom. > Plan C: > Get rid of 3rd party packages that eventually get in your way instead of > 'helping'. It's not django that is getting in the way. django is a fantastic framework for web development. It's just changing requirements. The original developer (who no longer works here) created a few different classes, and then based on the original requirements, chose to use the one that supports a single query. That worked then, but may not work now. There are other classes that allow multiple queries, but then you have to parse the data and stuff it into the context that will be picked up by the browser yourself. The single query class does that for you. I may yet go that way, but I'm trying to avoid a big rewrite if I can. >> -----Original Message----- >> From: Larry Martell [mailto:larry.mart...@gmail.com] >> Sent: Tuesday, September 18, 2012 4:57 AM >> To: peter.braw...@earthlink.net >> Cc: mysql@lists.mysql.com >> Subject: Re: getting certain rows from a group by >> >> On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley >> <peter.braw...@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
