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

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Select date time for the different status of the field (http://www.codingforums.com/showthread.php?t=275918)

chidambaram1987

10-09-2012 07:43 PM

Select date time for the different status of the field

I am looking for help to select the datatime of the particular status .. 1) Min date time for staus 1 and 2) Max date time for status 2

+---------------------+--------+
| online | status |
+---------------------+--------+
| 2012-10-09 21:30:12 | 1 |
| 2012-10-09 22:30:24 | 0 |
| 2012-10-09 23:30:44 | 1 |
| 2012-10-09 23:30:47 | 0 |
+---------------------+--------+

Min time of status 1 and max time of status 0 is required please

Old Pedant

10-09-2012 08:46 PM

Code:

SELECT MIN( IF(status=1,online,'2099-12-31') ) AS minStatus1,

       MAX( IF(status=2,online,'1900-1-1') ) AS maxStatus2

FROM table

Naturally, the "dummy" values there (2099-12-31 and 1900-1-1) can be anything you choose that are outside the possible range of value in your actual data.

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.

All times are GMT +1. The time now is 03:16 AM.