Search Postgresql Archives

Re: ORDER BY - problem with NULL values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux