How do I change sort order behavious with nulls

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

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.

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