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?