gedq gedq - 5 months ago 12
SQL Question

mysql - how can I select the min value if its status is equal to a particular value?

I need to pull all tests out of a table if they were the first test taken that day and they passed. Since MySQL only allows one field in a group by then I'm struggling to find a way to do it. I've had a hunt around, and so far I'm up to:

select distinct
uut_serial_number,
MIN(START_DATE_TIME) as 'first test passed'
from
uut_result
where
date(START_DATE_TIME) = '2016-06-16'
and
uut_status = 'passed'
group by uut_serial_number

union

select distinct
uut_serial_number,
MIN(START_DATE_TIME) as 'first test taken'
from
uut_result
where
date(START_DATE_TIME) = '2016-06-16'
group by uut_serial_number;


It's giving me two records for each serial number with two test times. Logically if first < last then the unit failed its first test and the pass is the second figure, so I could run through it in code and sort them that way, but is there a way to do it in the query?

Thanks

Answer

The difference between your two queries is only this part and uut_status = 'passed' in the where clause of the first. However, it is possible to apply an additional filter during aggregation using an case expression in the aggregate function:

select uut_serial_number,  
     , MIN(CASE WHEN uut_status = 'passed'  THEN START_DATE_TIME END) as 'first test passed' 
     , MIN(START_DATE_TIME) as 'first test taken' 
  from 
       uut_result 
 where
       date(START_DATE_TIME) = '2016-06-16' 
 group by uut_serial_number 

Only those rows satisfying uut_status = 'passed' are considered for this min expression (the others are ignored by min because aggregate functions ignore null and else null is implied for case when skipped).

You can then easily validate your first < last in the query itself (you have both in the same row now).

There is actually an less ugly way to write this in SQL, but MySQL doesn't support this out of the box. However, it might be easier to understand:

select uut_serial_number,  
     , MIN(START_DATE_TIME) FILTER(WHERE uut_status = 'passed') as 'first test passed' 
     , MIN(START_DATE_TIME) as 'first test taken' 
  from 
       uut_result 
 where
       date(START_DATE_TIME) = '2016-06-16' 
 group by uut_serial_number 

The FILTER following the aggregate function max just specifies which rows to consider during the aggregation.

I've recently written an article about filter that also explains how the work around with case works: