On Sat, 19 Feb 2005 12:01:07 -0600, Bruno Wolff III <bruno@xxxxxxxx> wrote:
On Sat, Feb 19, 2005 at 18:04:32 +0100, charlie clark <charlie@xxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
