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