Select Max Value of Each Group

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;

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).

Show only the max value of each group

If you just want the max amount, with respect to the date- you can take the MAX and cast the datetime as a date:

DECLARE @temp TABLE (groupno int, [user] varchar(20), status varchar(20), date datetime, amount int)

INSERT INTO @temp
VALUES
 (74,'user1', 'status_1', '2016-01-01 05:40:00.0', 900)
,(74,'user1', 'status_1', '2016-01-01 05:45:00.0', 1200)
,(79,'user1', 'status_2', '2016-01-01 05:45:31.0', 31)
,(79,'user1', 'status_2', '2016-01-01 05:50:00.0', 300)
,(79,'user1', 'status_2', '2016-01-01 05:55:00.0', 600)
,(79,'user1', 'status_2', '2016-01-01 06:00:00.0', 900)
,(79,'user1', 'status_2', '2016-01-01 06:05:00.0', 1200)
,(90,'user1', 'status_1', '2016-01-01 06:07:52.0', 172)
,(90,'user1', 'status_1', '2016-01-01 06:10:00.0', 300)
,(90,'user1', 'status_1', '2016-01-01 06:15:00.0', 600)


SELECT groupno, [user], [status], CAST([date] as date) [Date], MAX(amount) as MAXamount
FROM @temp
GROUP BY groupno, [user], [status], CAST([date] as date)

select max value of each group including other column

One option uses ROW_NUMBER:

SELECT Name, Value, AnotherColumn
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Value DESC) rn
    FROM yourTable
) t
WHERE rn = 1

Note that if you want all ties per name with regard to largest value, then you may replace ROW_NUMBER with RANK (or maybe DENSE_RANK), to get all ties.

Select the row with the maximum value in each group based on multiple columns in R dplyr

We may get rowwise max of the 'count' columns with pmax, grouped by 'col1', filter the rows where the max value of 'Max' column is.

library(dplyr)
df1 %>% 
 mutate(Max = pmax(count_col1, count_col2) ) %>%
 group_by(col1) %>%
 filter(Max == max(Max)) %>%
 ungroup %>%
 select(-Max)

-output

# A tibble: 3 × 4
  col1   col2   count_col1 count_col2
  <chr>  <chr>       <dbl>      <dbl>
1 apple  aple            1          4
2 banana banan           4          1
3 banana bananb          4          1

We may also use slice_max

library(purrr)
df1 %>%
  group_by(col1) %>%
  slice_max(invoke(pmax, across(starts_with("count")))) %>%
  ungroup
# A tibble: 3 × 4
  col1   col2   count_col1 count_col2
  <chr>  <chr>       <dbl>      <dbl>
1 apple  aple            1          4
2 banana banan           4          1
3 banana bananb          4          1

Select records with max property value per group

You can do that with the following Linq.

var results = data.GroupBy(r = r.GroupValue)
    .OrderByDescending(g => g.Key)
    .FirstOrDefault()
    ?.GroupBy(r => r.GroupName)
    .Select(g => g.OrderByDescending(r => r.MemberValue).First());

First you have to group on the GroupValue then order the groups in descending order by the Key (which is the GroupValue) and take the first one. Now you have all the rows with the max GroupValue. Then you group those on the GroupName and from those groups order the MemberValue in descending order and take the First row to get the row in each GroupName group with the max MemberValue. Also I'm using the C# 6 null conditional operator ?. after FirstOrDefault in case data is empty. If you're not using C# 6 then you'll need to handle that case up front and you can just use First instead.

Select rows with Max(Column Value) for each unique combination of two other columns

In MySQL 5.x you can use a sub-query.

SELECT * 
FROM your_table
WHERE (`Group`, Dataset, RunNumber) IN (
    SELECT `Group`, Dataset, MAX(RunNumber) AS MaxRunNumber
    FROM your_table
    GROUP BY `Group`, Dataset
  );

Test on db<>fiddle here

Alternatives

--
-- LEFT JOIN on bigger
--
SELECT t.* 
FROM your_table t
LEFT JOIN your_table t2
  ON t2.`Group` = t.`Group`
 AND t2.Dataset = t.Dataset
 AND t2.RunNumber > t.RunNumber
WHERE t2.RunNumber IS NULL
ORDER BY t.`Group`, t.Dataset;

--
-- where NOT EXISTS on bigger
--
SELECT * 
FROM your_table t
WHERE NOT EXISTS (
  SELECT 1
  FROM your_table t2
  WHERE t2.`Group` = t.`Group`
    AND t2.Dataset = t.Dataset
    AND t2.RunNumber > t.RunNumber
)
ORDER BY `Group`, Dataset;

--
-- Emulating DENSE_RANK = 1 with variables
-- Works also in 5.x
--
SELECT RunNumber, `Group`, Dataset, Total
FROM
(
  SELECT 
    @rnk:=IF(@ds=Dataset AND @grp=`Group`, IF(@run=RunNumber, @rnk, @rnk+1), 1) AS Rnk
  , @grp := `Group` as `Group`
  , @ds := Dataset as Dataset
  , @run := RunNumber as RunNumber
  , Total
  FROM your_table t
  CROSS JOIN (SELECT @grp:=null, @ds:=null, @run:=null, @rnk := 0) var
  ORDER BY `Group`, Dataset, RunNumber DESC
) q
WHERE Rnk = 1
ORDER BY `Group`, Dataset;

--
-- DENSE_RANK = 1
-- MySql 8 and beyond. 
--
SELECT *
FROM
(
  SELECT *
  , DENSE_RANK() OVER (PARTITION BY `Group`, Dataset ORDER BY RunNumber DESC) AS rnk
  FROM your_table
) q
WHERE rnk = 1
ORDER BY `Group`, Dataset;