Get Records With Max Value For Each Group of Grouped SQL Results

Get records with max value for each group of grouped SQL results

There's a super-simple way to do this in mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.

You avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)

Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.

Version 5.7 update:

Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).

SQL to get max value from each group

select * from [table] t1
inner join
(
select track_id, user_id, max(rating) maxRating
from [table]
group by track_id, user_id
) tmp
on t1.track_id = tmp.track_id
and t1.user_id = tmp.user_id
and t1.rating = tmp.maxRating;

mysql - Get records with max value for each group of grouped SQL results

You can use row_number():

select *
from (
    select t.*, row_number() over(partition by group order by age desc, person) rn
    from mytable t
) t
where rn = 1

The accepted answer to the linked post baffles me somehow. It relies on option ONLY_FULL_GROUP_BY being disabled (well, ok, that was the default in MySQL 5.6). It makes assumptions about the way MySQL behaves in that case, which I don't think are officially documented anywhere. I wouldn't recmmend that, even in MySQL 5.6; happily, there is another answer, with more upvotes, that seem like a much safer approach to the question.

Rows with max value of each group

You need a subselect:

SELECT yourtable.*
FROM yourtable
LEFT JOIN (
    SELECT grp_id, MAX(created) AS max
    FROM yourtable
    GROUP BY grp_id
) AS maxgroup ON (
    (yourtable.grp_id = maxgroup.grp_id) AND (yourtable.created = maxgroup.max)
)

subselect the gets the ID/max value for each group, and the parent/outer query joins agains the subselect results to get the rest of the fields for the row(s) that the max value appears on.

Return the row with max value for each group

A perfect use case for DISTINCT ON:

SELECT DISTINCT ON (realm, race) *
FROM   tbl
ORDER  BY realm, race, total DESC;

db<>fiddle here

Notably, the query has no GROUP BY at all.

Assuming total is NOT NULL, else append NULLS LAST.

In case of a tie, the winner is arbitrary unless you add more ORDER BY items to break the tie.

Detailed explanation:

• Select first row in each GROUP BY group?

Oracle SQL get row with MAX value for each group in a set of grouped results

You can use CTEs

WITH CTE0 AS 
(
  SELECT 
       REV_USAGE_DATA.DDATE, 
       REV_USAGE_DATA.SEGMENT, 
       COUNT(*) AS Freq
  FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
  GROUP BY 
       REV_USAGE_DATA.DDATE,
       REV_USAGE_DATA.SEGMENT
)
SELECT 
  DDATE,
  SEGMENT, 
  FREQ
FROM CTE0 
WHERE (DDATE, SEGMENT, FREQ) IN (
  SELECT DDATE, MAX(SEGMENT), MAX(FREQ)
  FROM CTE0 
  GROUP BY DDATE
)

---

Related Topics