Select records with max property value per group

.net, c#, group-by, linq

Solution

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.

With the addition of `MaxBy` in .Net 6 this can now be done as follows

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

Problem

I've got a data set like this: ``` GroupName GroupValue MemberName MemberValue 'Group1' 1 'Member1' 1 'Group1' 1 'Member2' 2 'Group2' 2 'Member3' 3 'Group2' 2 'Member4' 2 'Group3' 2 'Member5' 4 'Group3' 2 'Member6' 1 ``` What I want to select is the rows that have the maximum `MemberValue` per `GroupName`, but only for those `GroupName`s that have the largest `GroupValue`, and pass them into a delegate function. Like this: ``` 'Group2' 2 'Member3' 3 'Group3' 2 'Member5' 4 ``` So far I've tried this format... ``` data.Where(maxGroupValue => maxGroupValue.GroupValue == data.Max(groupValue => groupValue.GroupValue)) .Select(FunctionThatTakesData) ``` ...but that just gives me every member of Group2 and Group3. I've tried putting a `GroupBy()` before the `Select()`, but that turns the output into an `IGrouping<string, DataType>` so `FunctionThatTakesData()` doesn't know what to do with it, and I can't do another `Where()` to filter out only the maximum `MemberValue`s. What can I do to get this data set properly filtered and passed into my function?

Original source

Related problems