Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Last edited by chidambaram1987; 10-09-2012 at 08:45 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,205
    Thanks
    80
    Thanked 4,564 Times in 4,528 Posts
    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.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,205
    Thanks
    80
    Thanked 4,564 Times in 4,528 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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •