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)
>
> Looks easy.
>
> If I apply this to my SQL:
>
> SELECT
>     SUM(CASE WHEN year=2002 THEN value ELSE NULL END) AS "y_2002",
>     SUM(CASE WHEN year=2001 THEN value ELSE NULL END) AS "y_2001",
>     SUM(CASE WHEN year=2000 THEN value ELSE NULL END) AS "y_2000",
>     c.name AS name
> FROM
>     aquacult_prod_marine AS d
> LEFT JOIN
>     countries AS c ON c.id = id_country
> GROUP BY
>     name
> ORDER BY
>     y_2000 DESC
>
> I would then say:
>
> ORDER BY
>     (y_2000 is null),
>     y_2000 DESC
>
> But then I get an Error warning:
>
>     ERROR:  column "y_2000" does not exist
>
> What do I do wrong?

Hmm... Nothing.

The "ORDER BY" clause should get processed last, after column-aliasing
(which labels your column "y_2000"). However, it seems like PG is
evaluating the (X is null) clause earlier.

=> SELECT version,count(*) AS ct FROM items.wiki_items GROUP BY version
ORDER BY (ct IS NULL);
ERROR:  column "ct" does not exist
                                                              ^
=> SELECT version,count(*) AS ct FROM items.wiki_items GROUP BY version
ORDER BY (count(*) IS NULL);
...works...

I can see why, but it's a pain.

You've got two options:
1. Repeat the expression as I've done above
   ORDER BY (CASE (...) END IS NULL), y_2000 DESC
2. Wrap your query in another query so the column aliases are available:
   SELECT * FROM (<your query>) AS results ORDER BY y_2000 IS NULL,
y_2000 DESC


--
   Richard Huxton
   Archonet Ltd