Geek asks and answers

How to get only the latest records with the same identifier in a DataTable?

I have the following:

DataTable table = new DataTable();
table.Columns.Add("id", typeof(string));
table.Columns.Add("date", typeof(DateTime));

DateTime date1 = new DateTime(2008, 3, 1, 7, 0, 0);
DateTime date2 = new DateTime(2007, 3, 1, 7, 0, 0);
DateTime date3 = new DateTime(2006, 3, 1, 7, 0, 0);

table.Rows.Add("123", date1);
table.Rows.Add("123", date2);
table.Rows.Add("ABC", date3);

I want to run an action that will delete all rows that have the same identifier as the other row, and save only one with the newest date.

In this small example, at the beginning I:

123 2008...
123 2007...
ABC 2006...

After the action, it should be:

123 2008...
ABC 2006...

How can I understand that?

(this is just a small example, my real data is much more)

+3
source share
4 answers

Assuming you cannot directly filter the data in the data source (= configure the SQL query), you can delete the rows using the following code:

var multiDates = from dr in table.AsEnumerable()
                 group dr by dr.Field<string>("id") into grp
                 where grp.Count() > 1
                 select grp.OrderByDescending(gr => gr.Field<DateTime>("date"));

var toDelete = multiDates
    .SelectMany(rows => rows.Skip(1))
    .ToArray();

foreach (var row in toDelete)
    row.Delete();

table.AcceptChanges();
+2
source

, , DataTable DataView, , .

DataView dv = new DataView(DATA_TABLE, "", "ID, Date DESC", DataViewRowState.None);
for (int i = 1; i < dv.Count; i++)
{
    if (dv[i - 1].Row["ID"] == dv[i].Row["ID"])
    {
       dv[i].Delete();
       i--;
    }
 }

, - EndEdit_Event,

- dataSource (ArrayList, List, DataSet aso) businessLogic.

0

Here is one of the alternatives

 var rowsToDelete = 
              (from outer in table.AsEnumerable()
              where !(from inner in table.AsEnumerable()
                    group inner by inner.Field<string>("id") into grp
                    where grp.Key == outer.Field<string>("id") &&
                          grp.Max (g => g.Field<DateTime>("date")) == outer.Field<DateTime>("date")
                    select grp.Key).Any()
              select outer).ToList();

foreach (var row in rowsToDelete) 
   row.Delete();
table.AcceptChanges();
0
source

You may have a linq request for a single field id, e.g. below

var MyQry=(from P5 in table.AsEnumerable() select P5["id"]).Distinct().ToList();
for (int O1 = 0; O1 < MyQry.Count; O1++)
{
     richTextBox1.Text = MyQry[O1].ToString();
}
-1
source

More articles:


All Articles