On Wed, Sep 19, 2012 at 12:04 AM, <h...@tbbs.net> 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), > 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)
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 = 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
