Re: ORDER BY - problem with NULL values

Richard Huxton escribió:
> 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
>
>
From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST

I think this is what you want right?