7

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user30832
  • 71
  • 1
  • 3
  • Could you post the query that you used to create the first result? – crthompson Nov 21 '14 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 Nov 21 '14 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 Nov 23 '14 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 Nov 23 '14 at 22:09

2 Answers2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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: – user30832 Nov 23 '14 at 20:16
  • added my first code above, wondering if your suggestion can be added to make this all one query? – user30832 Nov 23 '14 at 20:29