Sort columns independently, such that all nulls are last per column

Question

Here is an example table called animal:

name | color
------------
fox  | brown
fox  | red
dog  | gold

Now, what I want is this result:

fox   | dog
-------------
brown | gold
red   |

The names should be columns of the result with the different color values as rows.

My first thought was like:

SELECT color
FROM animal
WHERE name='fox'

[some sort of join?]

SELECT color 
FROM animal
WHERE name='dog'

But I don't know what kind of join would do the trick.

Second thought:

SELECT CASE WHEN name = 'fox' THEN color ELSE NULL END AS fox,
CASE WHEN name = 'dog' THEN color ELSE NULL END AS dog
FROM animal

This returns:

fox   | dog
-----------
red   | 
brown |
      | gold

I would like to move the null values in this table to the end. I tried to:

ORDER BY CASE name
        WHEN 'fox' THEN fox
        WHEN 'dog' THEN dog
    END

But I'm not sure if this is really what I want and Postgres is nagging that fox is not a column although I can do ORDER BY fox.

Maybe my approach is total nonsense or there is some kind of coalesce magic that can do the trick?

What about ORDER BY something NULLS LAST ? – Frank Heikens Jan 02 '15 at 16:49 — Frank Heikens, Jan 02 '15 at 16:49

Erwin Brandstetter · Accepted Answer · 2022-05-08T23:53:07.083

You seem to be under the impression that there would be a "natural" order in a table (like in a spreadsheet), but there is not. Without ORDER BY, rows are returned in arbitrary order - which often happens to be identical to input order for small tables that have not been updated, yet.

WITH cte AS (
   SELECT row_number() OVER (PARTITION BY name ORDER BY color) AS rn, * 
   FROM   animal
   )
SELECT f.color AS fox, d.color AS dog
FROM        (SELECT rn, color FROM cte WHERE name = 'fox') f
FULL   JOIN (SELECT rn, color FROM cte WHERE name = 'dog') d USING (rn)
ORDER  BY rn;

Major points

Attach sequential numbers per color for each type of animal separately in a CTE.

The FULL [OUTER] JOIN is crucial, since the number of rows for 'fox' and 'dog' differ.

Colors are sorted alphabetically, NULL values are last automatically in default ASCENDING sort order. See:

Sort by column ASC, but NULL values first?

This only scans the table once.

Explanation for the error message you got ("fox" is not a column):

Thank you for this very clean approach. Upvoted. – RandomWalker Jun 07 '23 at 22:21 — RandomWalker, Jun 07 '23 at 22:21

score 1 · Answer 2 · answered Jan 02 '15 at 21:01

Try something like:

WITH fox_color as 
(SELECT color as fox, row_number() rn
 FROM animal
 WHERE name='fox'),
dog_color as 
(SELECT color as dog, row_number() rn
 FROM animal
 WHERE name='dog')
SELECT fox, dog
FROM fox_color 
JOIN dog_color ON fox_color.rn = dog_color.rn

Sort columns independently, such that all nulls are last per column

2 Answers2

Major points

Linked