My requirement is to fetch data using Linq, based on highest occurring value.
var Output = (from s in dt2.AsEnumerable()
from c in dt1.AsEnumerable()
where Regex.IsMatch(s.Field<string>("CODE1"), c.Field<string>("CODE1")) &&
Regex.IsMatch(s.Field<string>("SAMPLE"), c.Field<string>("CODE2"))
select new
{
OCCURANCE = c.Field<int>("OCCURANCE"),
CODE1 = s.Field<string>("CODE1"),
SAMPLE = s.Field<string>("SAMPLE"),
});
Receiving output as bellow -
| OCCURANCE | CODE1 | SAMPLE | |
|---|---|---|---|
| 1 | 120 | VP10 | OB-444 |
| 2 | 120 | VP10 | OC-555 |
| 3 | 120 | VP10 | OD-666 |
| 4 | 100 | VP10 | OD-666 |
From the above result set, 3rd and 4th row contains same data in CODE1 and SAMPLE column. But i need to fetch the record which has highest OCCURANCE value, hence I need to fetch row no 3 instead and exclude row no 4.
My required output is as given bellow -
| OCCURANCE | CODE1 | SAMPLE |
|---|---|---|
| 120 | VP10 | OB-444 |
| 120 | VP10 | OC-555 |
| 120 | VP10 | OD-666 |