title: Select multiple rows with the MAX SUM of a value grouped by another column
body: <p>I have a "resources" table that contains information about how resources of a specific weight are placed inside a territory by an user.</p>
  
  <pre><code>territory_id        user_id     weight 
  1                       1           1
  1                       1           4
  1                       1           2
  1                       2           2
  2                       3           2
  2                       2           3 
  2                       2           3
  3                       1           1
  4                       1           1
  4                       1           1
  4                       2           2
  4                       3           3
  4                       3           1
  4                       3           2
  5                       3           2
  5                       3           3
  5                       2           1
  4                       3           1
  </code></pre>
  
  <p>I want to calculate, for each existing territory, which user has the highest total weight of resources (and what is this value).
  So this should be an expected outcome for the previous data:</p>
  
  <pre><code>territory_id        best_user_id        best_user_total_weight_of_resources 
  1                       1                       7 
  2                       2                       6
  3                       1                       1
  4                       3                       6
  5                       3                       5
  </code></pre>
  
  <p>I have already tried several nested queries with <code>SUM</code>, <code>MAX</code>, <code>GROUP BY</code> but I really didn't find the proper way to calculate this.
  I have found a lot of similiar question, but not solving this exact problem.
  Any help? Thanks in advance!</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I found out right now that the double GROUP BY (i.e. "GROUP BY territory_id, user_id") with double ORDER BY partially solves my problem, but it shows also information that I don't want (not only the best user, but each single user that placed at least one resource).</p>
  
  <pre><code>SELECT territory_id, user_id AS best_user_id, SUM( weight ) AS best_user_total_weight
  FROM resources
  GROUP BY territory_id, user_id
  ORDER BY territory_id ASC, best_user_total_weight DESC;
  </code></pre>