Select records with max property value per group

Question

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 GroupNames 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 MemberValues.

What can I do to get this data set properly filtered and passed into my function?

Answer 1

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