From: Larry Martell
Date: September 19 2012 6:36pm
Subject: Re: getting certain rows from a group by
List-Archive: http://lists.mysql.com/mysql/228197
Message-Id: <CACwCsY5i3iXzGag_5VdwGrQLedwS3HdO2Gz7ns-bZ3VpQdY-9Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On Wed, Sep 19, 2012 at 12:04 AM,  <hsv@stripped> 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 =3D Min(bottom), bottom =3D Max(bottom),
> bottom closest to Avg(bottom), and bottom from the row where date_time
> =3D Max(date_time).
> <<<<<<<<
> There is a standard and ugly way of getting such a thing, but it entails =
repeating the table reference (supposing you want any, not every, to match)=
:
>
> SELECT data_cst.target_name_id, image_measurer_id, nb, xb, vb, xt, bottom
> FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) - =
bottom)) AS vb, Max(date_time) AS xt
> FROM data_cst
>       WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116,=
 117, 118, 119, 120, 121)
>       AND DATE(data_cst.date_time) =3D '2010-03-04'
>       GROUP BY target_name_id, ep, wafer_id
>       HAVING count(*) < 999) AS st
> JOIN data_cst ON st.target_name_id =3D data_cst.target_name_id AND (nb =
=3D bottom OR xb =3D bottom OR vb =3D bottom OR date_time =3D xt)

This:

MIN(ABS(Avg(bottom) - bottom))

Is not valid. It gives:

ERROR 1111 (HY000): Invalid use of group function

Which is why I was doing it with an order by. But I can figure out how
to work that into a join,

>
> One record will be returned for every row that holds a relevant extremum,=
 not guaranteed to be unique.
>
> This query pertains only to your original subquery, not the whole query. =
To get the result to which you refer, join this to your original query, to =
which you have added something like vb:
>
> SELECT *
> FROM (original query) JOIN (this query) ON nb =3D minbottom AND xb =3D ma=
xbottom AND xt =3D "Last Run" AND vb =3D .... AND (original query).target_n=
ame_id =3D (this_query).target_name_id
>
> --but I am not confident in the result. There are problems in the origina=
l query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom non=
e are aggregated over wafer_id. Therefore, it is not certain from which rec=
ord from q1 they are returned. MySQL tends to pick the first that fits, but=
 not only is nothing guaranteed, it is explicitly written that if such not =
aggregated fields appear, the output is not determinate unless all pertinen=
t are equal, the possibility wherof the reason for allowing it.
>
> When that has been handled, it is needful to change the foregoing query t=
o match that one in two levels, because averaging is not associative (there=
 is also the problem of equality-testing on generated floating-point number=
s). If it were only MAX and MIN, one level of not GROUPing BY wafer_id woul=
d be all right.
>
> By the way, in the original query, I suggest instead of
> data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND
> '2010-03-04 23:59:59'
> this,
> DATE(data_cst.date_time) =3D '2010-03-04'
>
> Instead of
>
>      ... q1,
>      data_target
> WHERE data_target.id =3D target_name_id
>
> this would be better:
>
>      ... q1 JOIN data_target ON data_target.id =3D target_name_id
>
> I believe that
>      100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100)
>    =3D 100*SUM(numbottoms)/SUM(numonep)
> and
>      SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END)
>    =3D SUM(GREATEST(reruns, 0))
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>