>>>> 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 where date_time
= 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) = '2010-03-04'
GROUP BY target_name_id, ep, wafer_id
HAVING count(*) < 999) AS st
JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb = bottom
OR xb = bottom OR vb = bottom OR date_time = xt)
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 = minbottom AND xb = maxbottom
AND xt = "Last Run" AND vb = .... AND (original query).target_name_id =
(this_query).target_name_id
--but I am not confident in the result. There are problems in the original
query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom none are
aggregated over wafer_id. Therefore, it is not certain from which record 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 pertinent are equal, the
possibility wherof the reason for allowing it.
When that has been handled, it is needful to change the foregoing query to
match that one in two levels, because averaging is not associative (there is
also the problem of equality-testing on generated floating-point numbers). If
it were only MAX and MIN, one level of not GROUPing BY wafer_id would 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) = '2010-03-04'
Instead of
... q1,
data_target
WHERE data_target.id = target_name_id
this would be better:
... q1 JOIN data_target ON data_target.id = target_name_id
I believe that
100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100)
= 100*SUM(numbottoms)/SUM(numonep)
and
SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END)
= SUM(GREATEST(reruns, 0))
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
