Keep a duplicate record with highest value in another column

Hi everyone. I have a dataset with many columns, and need to remove duplicates based on only a few of them (Below). But I need to keep the duplicate record with the highest value in another column.

My input and desired output are below:
image

Does anybody have any advice on how to best achieve my desired output? A linq query?

give a try at

Assign Activity:
dtResult =

(From d In YourDataTableVar.AsEnumerable()
Group d By k1 = d("Contract").toString.Trim, k2 = d("Order").toString.Trim Into grp=Group
Let grpo = grp.OrderBy(Function (x) CInt(System.Text.RegularExpressions.Regex.Match(x(2).toString, "\d+").Value))
Select r = grpo.Last()).CopyToDataTable
  • we group on the first 2 cols
  • order the group members on the number portion from 3rd col
  • taking the last ordered member
1 Like

DT.Asenumerable.Groupby(Function(r) r(0).Tostring).Select(Function(g) g.Orderby(Function(v) CInt(Regex.Match(v(2).Tostring,β€œ\d+$”).Value)).Last).Copytodatatable

Since I am bringing all of the data in as String, I changed CInt to CStr and it worked flawlessly. Thank you so much for the help and also for the helpful learning links!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.