Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in RPA by (5.3k points)

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.

1 Answer

0 votes
by (9.5k points)

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

Related questions

Browse Categories

...