Select records with max date in datatables Uipath

Question

Could you please help me with this?

I have a data table (Uipath) with the following fields: id, account, reg date, exp date. Each id might have more than one account. I need to select one record by id with max reg date.

In order to do this I am using:

(From row In data_dt Group By id = row(0) Into grp = Group Let md = grp.Max(Function (d) d(3)) Select data_dt_max.Rows.Add({id, CDate(md.ToString).ToString("dd/MM/yyyy")})).CopyToDataTable.

This instruction works but does not give me all the fields. I'm very rusty in programming, so I'm really not sure how to modify it or if I need a different approach.

Thanks very much for your help!

Answer 1

Try:

var selectedRows = data_dt
    .GroupBy(row => row.id)
    .Select(grp => grp
        .OrderByDescending(row => row.expDate)
        .First()
    );

This first groups by ID and then, within each group, selects the record with the latest expDate. The result should be just the rows with the max expDate in each group.

The above is uses LINQ method syntax. Perhaps someone else could suggest the LINQ query syntax equivalent.

Answer 2

// select all customers 
var customersWithMaxRegDate = data.GroupBy(x => x.Id)
                                  .Select(g => new
                                   {
                                       // grp by id
                                       Id = g.Key,
                                       regDate = g.Max( x => x.regDate),
                                       // get customer record with max regdate
                                       customer = g
                                   );