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)

=> SELECT * FROM (SELECT 1 as a UNION SELECT 2 UNION SELECT null::int)
AS foo ORDER BY (a is not null), a DESC;
a
---

2
1
(3 rows)

--
Richard Huxton
Archonet Ltd

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?

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

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 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

From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST

I think this is what you want right?

From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST

I think this is what you want right?

Yes, indeed. Sounds great..... unfortunately I am on 8.1. And
wouldn't really want to migrate to 8.3 and beta for the moment....

Thanks anyway!

Stef

Stefan Schwarzer ha scritto:

From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST

I think this is what you want right?

Yes, indeed. Sounds great..... unfortunately I am on 8.1. And wouldn't
really want to migrate to 8.3 and beta for the moment....

order by 1 ?

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

... ORDER BY y_2000 IS NULL [DESC], y_2000;

b) neglect the NULL values by still allowing the countries to be
displayed

Not quite sure what you mean by this.

--
Tommy Gildseth