CodingForums.com - View Single Post - Select date time for the different status of the field

Old Pedant · 10-09-2012, 08:50 PM

You could also force the code to get a NULL if there isn't any match on status=1 or status=2, if you need that:

Code:

SELECT IF( x.s1 = '2099-12-31', NULL, x.s1 ) AS minStatus1,
       IF( x.s2 = '1900-1-1', NULL, x.s2 ) AS maxStatus2
FROM (
    SELECT MIN( IF(status=1,online,'2099-12-31') ) AS s1,
           MAX( IF(status=2,online,'1900-1-1') ) AS s2
    FROM table
    ) AS x

But if you know there is at least one good value for status=1 and one good value for status=2 then don't bother with that.

10-09-2012, 08:50 PM	PM User \| #3
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,579 Thanks: 62 Thanked 4,063 Times in 4,032 Posts	You could also force the code to get a NULL if there isn't any match on status=1 or status=2, if you need that: Code: SELECT IF( x.s1 = '2099-12-31', NULL, x.s1 ) AS minStatus1, IF( x.s2 = '1900-1-1', NULL, x.s2 ) AS maxStatus2 FROM ( SELECT MIN( IF(status=1,online,'2099-12-31') ) AS s1, MAX( IF(status=2,online,'1900-1-1') ) AS s2 FROM table ) AS x But if you know there is at least one good value for status=1 and one good value for status=2 then don't bother with that. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.