From: | charlie clark <charlie(at)begeistert(dot)org> |
---|---|
To: | Bruno Wolff III <bruno(at)wolff(dot)to> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How do I change sort order behavious with nulls |
Date: | 2005-02-20 17:04:57 |
Message-ID: | opsmh9qjdtyt02yl@mail.isis.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, 19 Feb 2005 12:01:07 -0600, Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> On Sat, Feb 19, 2005 at 18:04:32 +0100,
> charlie clark <charlie(at)begeistert(dot)org> wrote:
>> Dear list,
>>
>> is there a simple way to change the way ORDER BY works on columns with
>> NULLs? I can understand the need for default behaviour but there must be
>> cases when this is undesirable. I have such a query with the NULLs
>> arising
>> as the result of an OUTER JOIN and I would like to ORDER BY DESC with
>> NULLs treated as <= 0. I've already tried a few things but nothing's
>> working so far.
>
> Presumably what you mean is that you want NULLs to be output last when
> doing a descending order by.
>
> You can do this using ORDER BY whatever IS NULL ASC, whatever DESC .
>
> If you really mean you want to treat them as less than or equal to
> 0, then you can pick such a value and use coalesce to change NULLs
> to that value in the ORDER BY clause.
Yes, this is what I want to do. It seems COALESCE is the clearest way to
do this.
SELECT COALESCE(mydate, timestamp'0000-01-01') AS mydate
FROM mytable
ORDER BY date DESC
There seems to be no penalty involved in running this as well.
Thank you very much
Charlie
--
Charlie Clark
Helmholtzstr. 20
Düsseldorf
From | Date | Subject | |
---|---|---|---|
Next Message | Larry Rosenman | 2005-02-20 17:05:44 | Re: Scalability with large numbers of tables |
Previous Message | Lincoln Yeoh | 2005-02-20 16:45:40 | Re: Scalability with large numbers of tables |