Professional Web Applications Themes

NULLS first ... - MySQL

Hi Is there a way to make NULLs appear first in this example: SELECT login_time FROM my_table GROUP BY id ORDER BY login_time DESC This gives me the list starting from most recent login and ending with NULL values. Is there a way to make NULLs appear first keeping the times data sorted like they are now....

  1. #1

    Default NULLS first ...

    Hi

    Is there a way to make NULLs appear first in this example:

    SELECT login_time FROM my_table GROUP BY id ORDER BY login_time DESC

    This gives me the list starting from most recent login and ending with
    NULL values. Is there a way to make NULLs appear first keeping the times
    data sorted like they are now.
    Ralph Guest

  2. #2

    Default Re: NULLS first ...

    Ralph wrote: 

    You can do it like this:

    SELECT login_time, IF(login_time IS NULL,1,0) AS sortkey FROM my_table
    ORDER BY sortkey DESC, login_time DESC

    There are probably better ways, too.

    Cheers,
    Nicholas Sherlock

    --
    http://www.sherlocksoftware.org
    Nicholas Guest

  3. #3

    Default Re: NULLS first ...

    Nicholas Sherlock wrote: 
    >
    > You can do it like this:
    >
    > SELECT login_time, IF(login_time IS NULL,1,0) AS sortkey FROM my_table
    > ORDER BY sortkey DESC, login_time DESC
    >
    > There are probably better ways, too.
    >
    > Cheers,
    > Nicholas Sherlock
    >[/ref]
    Thank you that helped :)
    Ralph Guest

  4. #4

    Default Re: NULLS first ...

    In article <eim9cv$rhi$ihug.co.nz>,
    Nicholas Sherlock <com> writes:
     [/ref]
     
     
     

    Yep:

    SELECT login_time
    FROM my_table
    GROUP BY id
    ORDER BY login_time IS NOT NULL, login_time DESC
    Harald Guest

  5. #5

    Default Re: NULLS first ...

    Harald Fuchs wrote: 

    Neat, thanks, I'll remember that one.

    Cheers,
    Nicholas Sherlock

    --
    http://www.sherlocksoftware.org
    Nicholas Guest

Similar Threads

  1. concat with nulls
    By rmorgan in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 13th, 03:12 PM
  2. empty strings vs nulls
    By Eric Walker in forum Perl / CGI
    Replies: 5
    Last Post: November 25th, 02:16 AM
  3. Casting nulls
    By Carl Furst in forum PHP Development
    Replies: 3
    Last Post: September 24th, 11:39 PM
  4. dealing with nulls....
    By Ken Schaefer in forum ASP
    Replies: 3
    Last Post: July 7th, 05:49 AM
  5. Problem with nulls
    By Bob in forum ASP.NET General
    Replies: 1
    Last Post: July 2nd, 08:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139