Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I change sort order behavious with nulls

How do I change sort order behavious with nulls

From
charlie clark
Date:
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.

Thanx for any pointers.

Charlie

--
Charlie Clark

Re: How do I change sort order behavious with nulls

From
Tom Lane
Date:
charlie clark <charlie@begeistert.org> writes:
> is there a simple way to change the way ORDER BY works on columns with
> NULLs?

No, but you can do something like

    ORDER BY foo IS NOT NULL, foo DESC

to make the nulls come first.

            regards, tom lane

Re: How do I change sort order behavious with nulls

From
Bruno Wolff III
Date:
On Sat, Feb 19, 2005 at 18:04:32 +0100,
  charlie clark <charlie@begeistert.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.

Re: How do I change sort order behavious with nulls

From
charlie clark
Date:
On Sat, 19 Feb 2005 12:01:07 -0600, Bruno Wolff III <bruno@wolff.to> wrote:

> On Sat, Feb 19, 2005 at 18:04:32 +0100,
>   charlie clark <charlie@begeistert.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