Re: ORDER BY - problem with NULL values

Stefan Schwarzer wrote:
> Hi there,
>
> if I order a given year in DESCending ORDER, so that the highest values
> (of a given variable) for the countries are displayed at the top of the
> list, then actually the NULL values appear as first. Only below, I find
> the values ordered correctly.
>
> Is there any way to
>
> a) make the countries with NULL values appear at the bottom of the list
> b) neglect the NULL values by still allowing the countries to be
> displayed

Not sure what you mean by (b), but (a) is straightforward enough.

=> SELECT * FROM (SELECT 1 as a UNION SELECT 2 UNION SELECT null::int)
AS foo ORDER BY (a is null), a DESC;
a
---
2
1

(3 rows)

=> SELECT * FROM (SELECT 1 as a UNION SELECT 2 UNION SELECT null::int)
AS foo ORDER BY (a is not null), a DESC;
a
---

2
1
(3 rows)

--
Richard Huxton
Archonet Ltd