title: PostgreSQL ranking query
body: <p>I am trying to do something similar to <a href="https://stackoverflow.com/questions/9424822/postgresql-getting-ordinal-rank-row-index-efficiently">this</a> but with the extra complication that I need to group by a common field</p>
  <p>I have two tables, one for <code>competition_users</code> (for those competing in competitions) and another <code>competition_times</code> (to store their fastest times), but there are many different competitions in the same table.</p>
  <p>I have created a new column <code>rank</code> in the <code>competition_times</code> table and now need to run an update for existing users to show their rank in their competition, I am trying to modify the solution given in the other SO answer without success</p>
  <p>The tables are as follows...</p>
  <p><strong>competition_users:</strong></p>
  <p><code>competition_user_id</code>, <code>competition_id</code></p>
  <p><strong>competition_times:</strong></p>
  <p><code>competition_time_id</code>, <code>competition_user_id</code>, <code>time_in_seconds</code>, <code>rank</code></p>
  <p>I am not sure if its possible to use <code>GROUP BY</code>? or if there is another way, so far I am trying something like this...</p>
  <pre><code>UPDATE competition_times
  SET    rank = r.rnk
  FROM (
      SELECT competition_time_id
           , dense_rank() OVER (ORDER BY time_in_seconds ASC) AS rnk
      FROM competition_times, competition_users
      WHERE competition_times.competition_user_id = competition_users.competition_user_id
      GROUP BY competition_users.competition_id
      ) r
  WHERE competition_times.competition_time_id = r.competition_time_id
  </code></pre>
  <p>Using postgreSQL 9</p>
  <p>Thanks</p>