Блог

У меня возникла проблема с выбором строк только с максимальными значениями из столбца ProblemsAmount, который представляет COUNT (*) из внутреннего запроса. Это выглядит как:

 PersonID   | PersonName   | ProblemID   | ProblemsAmount
1          | Johny        | 1           | 10
1          | Johny        | 2           | 5
1          | Johny        | 3           | 18
2          | Sara         | 4           | 2
2          | Sara         | 5           | 12
3          | Katerina     | 6           | 17
3          | Katerina     | 7           | 2
4          | Elon         | 8           | 20
5          | Willy        | 9           | 6
5          | Willy        | 10          | 2
  

Что я хочу получить:

 PersonID   | PersonName   | ProblemID   | ProblemsAmount
1          | Johny        | 3           | 18
2          | Sara         | 5           | 12
3          | Katerina     | 6           | 17
4          | Elon         | 8           | 20
5          | Willy        | 9           | 6
  

Код, который у меня есть прямо сейчас:

 SELECT A.PersonID,
       A.PersonName,
       A.ProblemID,
       MAX(A.ProblemsCounter) AS ProblemsAmount
FROM (SELECT Person.PersonId AS PersonID,
             Person.Name AS PersonName,
             Problem.ProblemId AS ProblemID,
             COUNT(*)      AS ProblemsCounter
      FROM Person,
           Problem
      WHERE Problem.ProblemId = Person.ProblemId
      GROUP BY Person.PersonId, Person.Name, Problem.ProblemId
     ) A
GROUP BY A.PersonID, A.PersonName, A.ProblemID
ORDER BY A.PersonName, ProblemsAmount DESC;
  

Внутренний запрос возвращает то же самое, что и внешний, я путаю с функцией MAX. Это не работает, и я не понимаю почему. Я пытался исправить это с помощью HAVING, но это не удалось.

Заранее спасибо.

Простой метод, который не требует подзапроса, — это TOP (1) WITH TIES и ROW_NUMBER() :

 SELECT TOP (1) WITH TIES p.PersonId, p.Name AS PersonName,
       pr.ProblemId, COUNT(*) AS ProblemsCounter
FROM Person p JOIN
     Problem pr
     ON pr.ProblemId = p.ProblemId
GROUP BY p.PersonId, p.Name, pr.ProblemId
ORDER BY ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY COUNT(*) DESC);
  

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

нет необходимости в подзапросе попробуйте, как показано ниже, и избегайте соединения, разделенного комой

  SELECT          Person.PersonID , 
                 Person.Name AS PersonName,
                 COUNT(Problem.ProblemId)  AS ProblemsCounter
                 ,max(ProblemsAmount) as ProblemsAmount
          FROM Person left join
               Problem
          on Problem.ProblemId = Person.ProblemId
          GROUP BY Person.PersonID, Person.Name