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