Как отобразить значение с наивысшем параметром в списке?

Question

[Megas]

Вот упрощенная схема БД:



У каждой группы есть множество юзеров и каждый юзер может состоять в множестве групп.

Надо отобразить все группы и для каждой отобразить имя юзера у которого значение score максимальное.

Чтоб получить такого юзера для какойто конкретно группы, я делаю так:

SELECT *
FROM users
WHERE id IN (
    SELECT user_id FROM user_to_groups WHERE group_id = XX
)
ORDER BY score DESC
LIMIT 1

Но теперь надо это сделать для списка всех групп. Я попробовал сделать нечто подобное:

SELECT g.*, best.*
FROM groups as g
LEFT JOIN (
    SELECT *
    FROM users
    WHERE id IN (
        SELECT user_id FROM user_to_groups WHERE group_id = g.id
    )
    ORDER BY score DESC
    LIMIT 1
) as best

Но само собой это не корректный запрос и поэтому он не работает.
Ктото знает как эта проблема решается?

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

Answer 1

[Андрей]

select
g.*
max(u.score)
from g
join u2g USING(group_id)
join u USING(user_id)
group by g.group_id

Comments

[Megas]

Если я правильно понимаю ваш запрос, то в нем я получу информацию о группе и максимальную оценку у юзера для этой группы, но не информацию о самом юзере.

Answer 2

[Rsa97]

Что-то наподобие такого:

SELECT `g`.`name`, `u`.`name`, `u`.`score`
  FROM (
    SELECT `ug`.`group_id` AS `group_id`, MAX(`u`.`score`) AS `score`
      FROM `user_to_groups` AS `ug`
      JOIN `users` AS `u` ON `u`.`id` = `ug`.`user_id`
      GROUP BY `ug`.`group_id`
  ) AS `m`
  JOIN `user_to_groups` AS `ug` ON `ug`.`group_id` = `m`.`group_id`
  JOIN `users` AS `u` ON `u`.`id` = `ug`.`user_id` AND `u`.`score` = `m`.`score`
  JOIN `groups` AS `g` ON `g`.`id` = `m`.`group_id`

Только если в группе будет несколько пользователей с максимальным `score`, то выдаст их всех.

Comments

[Megas]

Проблема в запросе в том, что если есть группа без единого юзера, то эта группа вообще не отобразится в списке.

[Rsa97]

Megas: Не отобразится. Если такая нужна, то можно изменить порядок JOIN'ов.

SELECT `g`.`name`, `u`.`name`, `u`.`score`
  FROM `groups` AS `g`
  LEFT JOIN `user_to_groups` AS `ug` ON `ug`.`group_id` = `g`.`id`
  LEFT JOIN (
    SELECT `ug`.`group_id` AS `group_id`, MAX(`u`.`score`) AS `score`
      FROM `user_to_groups` AS `ug`
      JOIN `users` AS `u` ON `u`.`id` = `ug`.`user_id`
      GROUP BY `ug`.`group_id`
  ) AS `m` ON `m`.`group_id` = `g`.`id`
  LEFT JOIN `users` AS `u` ON `u`.`id` = `ug`.`user_id` AND `u`.`score` = `m`.`score`

[Megas]

Rsa97: Вникнув в ваш ответ понял в чем проблема. Дело в том, что реальный пример на много сложнее, поэтому дополнительный вопрос: а что если нельзя сделать поиск по score? Что если нельзя написать u.score = m.score? Есть ли другой способ получить искомую информацию?