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 toa) 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 displayedNot 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?
<<attachment: smime.p7s>>
