From: | Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ORDER BY - problem with NULL values |
Date: | 2007-10-10 09:55:31 |
Message-ID: | DD60869E-B761-4C3F-A15A-14E15FC260B0@grid.unep.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-10-10 10:12:43 | Re: ORDER BY - problem with NULL values |
Previous Message | Dmitry Koterov | 2007-10-10 09:45:33 | Re: How to speedup intarray aggregate function? |