Search code examples
mysqlsqlmaxwhere-clausegreatest-n-per-group

non aggregate with MAX, and GROUP BY

I have the following table that comes from a subquery:

+----------+--------+--------+
| category | Number | Person |
+----------+--------+--------+
| Fishing  |      3 | p1     |
| Fishing  |    31  | p2     |
| Fishing  |    32  | p3     |
| Reading  |    25  | p2     |
| Reading  |    45  | p3     |
| Reading  |     8  | p1     |
| Surfing  |     8  | p1     |
| Surfing  |    17  | p3     |
| Surfing  |    20  | p2     |
+----------+--------+--------+

I'm trying to get a response that has the MAX for each category, like surfing 20 p2. But when I try to MAX on 'number', and group by 'category', I get an error for non-aggregate 'person'.

Here's where I've gotten so far:

SELECT
    subQry.category,
    subQry.number,
    subQry.person
FROM
    (
    #complicated multiple joins to get the summed up table above.
) AS subQry

As stated if I do MAX(subQry.total), and GROUP BY 'Category', I get that nonaggregate error on 'person'.

I've tried a couple of things, but I'm new and don't quite understand how they fit with my scenario. One which made sense but threw an error was the highest answer from here: Get records with max value for each group of grouped SQL results , but I can't self join on subQry, error says its not a table.

any help would be appreciated to get:

+----------+--------+--------+
| category | Number | Person |
+----------+--------+--------+
| Fishing  |    32  | p3     |
| Reading  |    45  | p3     |
| Surfing  |    20  | p2     |
+----------+--------+--------+
Solution

  • You need to filter, not to aggregate. One option uses window functions, available in MySQL 8.0:

    select *
from (
    select t.*, rank() over(partition by category order by number desc) rn
    from (...) t
) t
where rn = 1

    In normal situations, you should be able to push down the window functions to within your complicated subquery, which would save one level of nesting.

    Note that rank() allows ties, if any. If you don't want that, you can use row_number() instead - but then, your result will not be stable if there are ties: it would be preferable to have another sorting criteria to break the ties.