Выберите несколько строк с максимальной суммой значения, сгруппированного по другому столбцу

Question

У меня есть таблица "ресурсов", которая содержит информацию о том, как ресурсы определенного веса размещаются пользователем на территории.

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

Я хочу рассчитать для каждой существующей территории, какой пользователь имеет наибольший общий вес ресурсов (и какова эта стоимость). Так что это должно быть ожидаемым результатом для предыдущих данных:

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

Я уже пробовал несколько вложенных запросов с SUM, MAX, GROUP BY но я действительно не нашел правильный способ рассчитать это. Я нашел много похожих вопросов, но не решил именно эту проблему. Любая помощь? Заранее спасибо!

РЕДАКТИРОВАТЬ:

Прямо сейчас я обнаружил, что двойной GROUP BY (то есть "GROUP BY территория_ид, user_id") с двойным ORDER BY частично решает мою проблему, но он также показывает информацию, которую я не хочу (не только лучший пользователь, но и каждый из них). пользователь, который разместил хотя бы один ресурс).

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;

Answer 1

Вы можете запустить первый запрос, чтобы определить SUM(weight) для каждой пары (territory_id,user_id), а затем запустить второй SELECT запрос по этому результирующему набору для получения строки, соответствующей значению max summ:

SELECT territory_id, user_id, MAX(summ) 
FROM (
  SELECT territory_id, user_id, SUM(weight) AS summ 
  FROM resources
  GROUP BY territory_id, user_id
) AS t1 
GROUP BY territory_id