Display column name with max value between several columns

max, multiple-columns, postgis, postgresql, sql

Solution

That's a textbook example for a "simple" or "switched" `CASE` statement to avoid code repetition.

SELECT CASE greatest(denver, seattle, new_york, dallas, "san fran")
          WHEN denver      THEN 'denver'
          WHEN seattle     THEN 'seattle'
          WHEN new_york    THEN 'new_york'
          WHEN dallas      THEN 'dallas'
          WHEN "san fran"  THEN 'san fran'
       END AS city, *
FROM   tbl;

The first in the list (from left to right) wins in case of a tie.

Problem

I have data I have collect from a form. And have "pivoted" the data so it looks like this: ``` COUNTY | denver | seattle | new_york | dallas | san fran -----------+---------+-----------+----------+----------+--------- ada | 3 | 14 | 0 | 0 | 0 slc | 10 | 0 | 0 | 0 | 9 canyon | 0 | 5 | 0 | 0 | 0 washington | 0 | 0 | 11 | 0 | 0 bonner | 0 | 0 | 0 | 2 | 0 ``` (This was accomplished using case statements, crosstab is not allowed in the environment I am using: cartodb) I now need a column that list the `CITY` with the max value. For example: ``` COUNTY | CITY | denver | seattle | new_york | dallas | san fran -----------+----------+---------+-----------+----------+----------+--------- ada | seattle | 3 | 14 | 0 | 0 | 0 slc | denver | 10 | 0 | 0 | 0 | 9 canyon | seattle | 0 | 5 | 0 | 0 | 0 washington | new_york | 0 | 0 | 11 | 0 | 0 bonner | dallas | 0 | 0 | 0 | 2 | 0 ```

Original source