I try to remove the duplicate rows by select a first row from every group. For Example
PK Col1 Col2
1 A B
2 A B
3 C C
4 C C
I want a return:
PK Col1 Col2
1 A B
3 C C
I tried following code but it didn't work:
DataTable dt = GetSampleDataTable(); //Get the table above.
dt = dt.Select("SELECT MIN(PK), Col1, Col2 GROUP BY Col1, Col2);
This question is related to
c#
datatable
duplicates
dt = dt.AsEnumerable().GroupBy(r => r.Field<int>("ID")).Select(g => g.First()).CopyToDataTable();
dt.AsEnumerable()
.GroupBy(r => new { Col1 = r["Col1"], Col2 = r["Col2"] })
.Select(g =>
{
var row = dt.NewRow();
row["PK"] = g.Min(r => r.Field<int>("PK"));
row["Col1"] = g.Key.Col1;
row["Col2"] = g.Key.Col2;
return row;
})
.CopyToDataTable();
This solution sort by Col1 and group by Col2. Then extract value of Col2 and display it in a mbox.
var grouped = from DataRow dr in dt.Rows orderby dr["Col1"] group dr by dr["Col2"];
string x = "";
foreach (var k in grouped) x += (string)(k.ElementAt(0)["Col2"]) + Environment.NewLine;
MessageBox.Show(x);
Source: Stackoverflow.com