Display column name with max value between several columns

Question

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

Could you post the query that you used to create the first result? — crthompson
– crthompson, Commented Nov 21, 2014 at 17:30
(i) Do you still have the original (not pivoted) data accessible in the database holding above table? What's the table structure? (ii) What should happen if more than a single city has the maximum value for a certain county? — Abecee
– Abecee, Commented Nov 21, 2014 at 18:58
I posted the original query above (this is what created the pivot for me). My data is coming in from a google form that is why I am pivoting it. If there are more then to cities that have the same max I would like to include both, but am willing to just select the first one. — user30832
– user30832, Commented Nov 23, 2014 at 21:45
The guideline is make it one issue per question. If you have another question, just open another question. You can always reference this one for context. By later adding more questions you render complete answers incomplete. More importantly, the question loses value for the general public. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 23, 2014 at 22:09

Erwin Brandstetter · Accepted Answer · 2023-06-15 01:49:32Z

11

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.

edited Jun 15, 2023 at 1:49

answered Nov 21, 2014 at 22:04

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user30832 Over a year ago

added my first code above, wondering if your suggestion can be added to make this all one query?

Gordon Linoff · Accepted Answer · 2014-11-23 20:51:16Z

You can do this with a big case statement:

select t.*,
       (case when denver = greatest(denver, seattle, new_york, dallas, sanfran) then 'denver'
             when seattle = greatest(denver, seattle, new_york, dallas, sanfran) then 'seattle'
             when new_york = greatest(denver, seattle, new_york, dallas, sanfran) then 'new_york'
             when dallas = greatest(denver, seattle, new_york, dallas, sanfran) then 'dallas'
             when sanfran = greatest(denver, seattle, new_york, dallas, sanfran) then 'sanfran'
        end) as City                 
from table t;

EDIT:

I would pivot the results at the very end. Something like this:

SELECT name, state, the_geom,
       MAX(CASE WHEN seqnum = 1 THEN favorite_team END) as favorite_team,
       MAX(CASE WHEN favorite_team = 'Arizona Cardinals' THEN cnt ELSE 0 END) as ari,
       MAX(CASE WHEN favorite_team = 'Atlanta Falcons' THEN cnt ELSE 0 END) as atl,
       MAX(CASE WHEN favorite_team = 'Baltimore Ravens' THEN cnt ELSE 0 END) as bal,
       MAX(CASE WHEN favorite_team = 'Buffalo Bills' THEN cnt ELSE 0 END) as buf
FROM (SELECT c.name, c.state, c.the_geom, s.favorite_team, count(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY c.name, c.state, c.the_geom ORDER BY COUNT(*) desc) as seqnum
      FROM fandom_survey_one s JOIN
           counties c
           ON ST_Intersects(s.the_geom, c.the_geom)
      GROUP BY c.name, c.state, c.the_geom, s.favorite_team
     ) c
GROUP BY name, state, the_geom
ORDER BY name, state

Thanks for the reply. I am wondering if I can make this happen in one query. For example, here is the query I used to get my data into the first table example above:
added my first code above, wondering if your suggestion can be added to make this all one query?

Collectives™ on Stack Overflow

Display column name with max value between several columns

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related