Обсуждение: How do I change sort order behavious with nulls
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
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