Re: getting certain rows from a group by

2012-09-20 Thread Larry Martell
ottom 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", >> >

Re: getting certain rows from a group by

2012-09-19 Thread hsv
2012/09/19 14:36 -0400, Larry Martell MIN(ABS(Avg(bottom) - bottom)) Is not valid. It gives: ERROR (HY000): Invalid use of group function Yes, I had my doubts of that, for all that I suggested it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/m

RE: getting certain rows from a group by

2012-09-19 Thread Rick James
t; -Original Message- > From: Larry Martell [mailto:larry.mart...@gmail.com] > Sent: Wednesday, September 19, 2012 3:22 PM > To: Rick James > Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com > Subject: Re: getting certain rows from a group by > > Well, I'm gettin

Re: getting certain rows from a group by

2012-09-19 Thread Larry Martell
@max = MAX(bottom), ... ) >>>> >> >JOIN data_cst mn ON bottom = @min >>>> >> > JOIN data_cst mx ON bottom = @max >>>> > >>>> > That is, the FROM finds the value for the desired row. >>>> > The JOIN then ge

Re: getting certain rows from a group by

2012-09-19 Thread Larry Martell
'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 > join data_cst mn ON mn.bottom = q1.minbottom > join data_cst mx on mx.bottom = q1.maxbottom > join data_cst lr on lr.date_time = q

Re: getting certain rows from a group by

2012-09-19 Thread Larry Martell
On Wed, Sep 19, 2012 at 10:26 AM, Larry Martell wrote: > On Wed, Sep 19, 2012 at 12:04 AM, wrote: >> 2012/09/18 06:53 -0400, Larry Martell >> 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 gro

Re: getting certain rows from a group by

2012-09-19 Thread Larry Martell
On Wed, Sep 19, 2012 at 12:04 AM, wrote: > 2012/09/18 06:53 -0400, Larry Martell > 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), > bott

Re: getting certain rows from a group by

2012-09-19 Thread Larry Martell
On Wed, Sep 19, 2012 at 12:04 AM, wrote: > 2012/09/18 06:53 -0400, Larry Martell > 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), > bott

Re: getting certain rows from a group by

2012-09-19 Thread Larry Martell
9) q1 join data_cst mn ON mn.bottom = q1.minbottom join data_cst mx on mx.bottom = q1.maxbottom join data_cst lr on lr.date_time = q1.LastRun, data_target WHERE data_target.id = q1.target_name_id GROUP BY q1.target_name_id, q1.ep; > >> -Original Message- >> From: Larry M

Re: getting certain rows from a group by

2012-09-18 Thread hsv
2012/09/18 06:53 -0400, Larry Martell 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 w

RE: getting certain rows from a group by

2012-09-18 Thread Rick James
M > To: Rick James > Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com > Subject: Re: getting certain rows from a group by > > On Tue, Sep 18, 2012 at 4:01 PM, Rick James > wrote: > > SELECT mn.* > >mx.* > >> >FROM ( SELECT @min = MIN(botto

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
tom`); Any way to work that into another join? Thanks! > >> -Original Message- >> From: Larry Martell [mailto:larry.mart...@gmail.com] >> Sent: Tuesday, September 18, 2012 12:54 PM >> To: Rick James >> Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com >&

RE: getting certain rows from a group by

2012-09-18 Thread Rick James
rself. 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,

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
ail.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 >> wrote: >> > On

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
On Tue, Sep 18, 2012 at 7:56 AM, Larry Martell wrote: > On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley > 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, >>>

RE: getting certain rows from a group by

2012-09-18 Thread Rick James
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 > wrote: > > On 2012-09-18 5:53 AM, Larry Martell wrote: > >> > >> I have this query: > >> > >> SELECT data_t

Re: getting certain rows from a group by

2012-09-18 Thread Larry Martell
On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley 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 T

Re: getting certain rows from a group by

2012-09-18 Thread Peter Brawley
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",