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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
