How to get ID that has a maximum value of Hits by grouping Date by in LINQ

Question

I have a DataTable that has the sample data like this:

ID | HITS | Date 1 | 12 | 1 October 2017 2 | 15 | 1 October 2017 3 | 13 | 1 October 2017 4 | 5 | 1 November 2017 5 | 7 | 1 November 2017 6 | 3 | 1 November 2017

The result that I want is:

ID 2 ==> 15 is the max value for hits in Oct 2017 5 ==> 7 is the max value for hits in Nov 2017

Prefer in VB.NET. We can do this in TSQL BUT since the data is in DataTable already so we have to manipulate at Linq level.

Answer 1

Four things that are mandatory to run this code are:

• Group by date

• Order each group by hit count in descending order

• Pick the first element of each group

• Take its ID

var res = dataTable.AsEnumerable() .Select(r => new { Id = r.Field<int>("Id") , Hits = r.Field<int>("Hits") , Date = r.Field<DateTime>("Date") // Use proper type here }) .GroupBy(r => r.Date) .Select(g => new { Id = g.OrderByDescending(r => r.Hits).First().Id , Date = g.Key ) .ToList();