Does the SELECT with the maximum function change overtime in this exercise?-CodePudding

I tried to solve this SQL exercise with this query, but I cannot understand if it can work because of this part:

HAVING COUNT(*) = (
SELECT MAX(TotalP)
FROM (SELECT AuthorCode, Edition, Conference, Count(*) TotalP
FROM AUTHOR_PRESENTS_ARTICLE APA1
WHERE APA1.Edition = APA.Edition AND APA1.Conference = APA.Conference
GROUP BY AuthorCode, Edition, Conference))

doesn't the SELECT return the same value (the maximum) every time, so I can't have for every edition of the conference? I attach my complete code below:

SELECT EC.EditionName,
       APA.AuthorCode
FROM   EDITIONS_OF_THE_CONFERENCE EC,
       AUTHOR_PRESENTS_ARTICLE APA
WHERE  EC.Conference = APA.Conference
AND    EC.Conference IN(
         SELECT Conference
         FROM EDITIONS_OF_CONFERENCE
         GROUP BY Conference  --L'EDITION SARA' DIVERSA ESSENDO ENTRAMBE PK
         HAVING COUNT(*) >= 10
       )
GROUP BY APA.Edition,
         APA.EditionName,
         APA.Conference,
         APA.AuthorCode
HAVING COUNT(*) = (
                    SELECT MAX(TotalP)
                    FROM   (
                      SELECT AuthorCode,
                             Edition,
                             Conference,
                             Count(*) TotalP
                      FROM   AUTHOR_PRESENTS_ARTICLE APA1
                      WHERE  APA1.Edition = APA.Edition
                      AND    APA1.Conference = APA.Conference
                      GROUP BY AuthorCode, Edition, Conference
                    )
)

CodePudding user response：

doesn't the SELECT return the same value (the maximum) every time,

No, it does not return the same maximum each time as you have correlated the sub-query to the outer query.

If you had used:

SELECT MAX(TotalP)
FROM   (
  SELECT AuthorCode,
         Edition,
         Conference,
         Count(*) TotalP
  FROM   AUTHOR_PRESENTS_ARTICLE APA1
  GROUP BY AuthorCode, Edition, Conference
)

Then it would return the maximum count for all the articles but you did not and added a WHERE filter:

SELECT MAX(TotalP)
FROM   (
  SELECT AuthorCode,
         Edition,
         Conference,
         Count(*) TotalP
  FROM   AUTHOR_PRESENTS_ARTICLE APA1
  WHERE  APA1.Edition = APA.Edition
  AND    APA1.Conference = APA.Conference
  GROUP BY AuthorCode, Edition, Conference
)

That restricts it to finding the maximum for that conference and edition from the outer query so when you have a different conference or a different edition then you will find the maximum for that specific pairing.