title: SQL: Selecting top proportion of column and corresponding attributes in other columns
body: <p>Say I have data like so</p>
  <pre><code>group,      city,  user,
  nyc_frisbee, nyc,   joe,
  nyc_frisbee, nyc,   ben,
  nyc_frisbee,  sf,   tim,
  sf_cooking,   sf,   tim,
  sf_cooking,  atl,   jon, 
  </code></pre>
  <p>My goal is to get</p>
  <pre><code>group,       top_city, prob
  nyc_firsbee, nyc,      .66
  sf_cooking,  sf,       .5
  sf_ccoking,  atl,      .5
  </code></pre>
  <p>Edge case: In this table below, nyc_frisbee shows up only once because there is a single top city. However, sf_cooking shows up twice because sf and atl are tied.</p>
  <p>How can I accomplish this in SQL?</p>
  <p>What I have so far:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT group, city, top/total AS prob
  FROM (
       SELECT 
           group, 
           city, 
           freq,
           MAX(freq) OVER(PARTITION BY group) AS top,
           SUM(freq) OVER(PARTITION BY group) AS total
       FROM
           (SELECT group, city, COUNT(city) AS freq
            FROM mytable
            GROUP BY group, city) inner_query
       ) outer_query
  WHERE outer.freq = outer.top 
  </code></pre>
  <p>This doesn't behave the way that I intended...</p>
  <pre><code>group,       top_city, prob
  nyc_firsbee, nyc,      .66
  nyc_firsbee, sf,       .66
  sf_cooking,  sf,       .5
  sf_ccoking,  atl,      .5
  </code></pre>
  <p>So I'm getting one row per each unique group/city combination with the same prob value.</p>