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 wrote:
> 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.

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 Sherlock wrote:
> Ralph wrote:
>> 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.
>
> 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
>
Thank you that helped :)

In article <eim9cv$rhi$1@lust.ihug.co.nz>,
Nicholas Sherlock <N.sherlock@gmail.com> writes:

> Ralph wrote:
>> 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.

> 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.

Yep:

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

Harald Fuchs wrote:
> ORDER BY login_time IS NOT NULL, login_time DESC

Neat, thanks, I'll remember that one.

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org