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