none
LINQ - Rows Max Date of Group RRS feed

  • Question

  • I am trying to use LINQ to return a collection of rows, containing only the latest row contained in a group.

    Sample Data:

    Source

     

    Desired Result

    PartNo

    SerialNo

    Date

     

    PartNo

    SerialNo

    Date

    A001

    1048

    06/04/2019

    A001

    1048

    06/04/2019

    A001

    1070

    06/05/2019

    A001

    1070

    06/05/2019

    A002

    1009

    06/13/2019

     

    A002

    1009

    08/19/2019

    A002

    1009

    08/19/2019

    A003

    1018

    07/10/2019

    A003

    1018

    07/10/2019

    A003

    1019

    07/12/2019

     

    A003

    1019

    07/23/2019

     

    A003

    1019

    08/26/2019

    A003

    1019

    08/26/2019

    The following code is working and returns all rows for a target "PartNo" ordered by "Date":

    Dim Rows As EnumerableRowCollection(Of datarow) = From r In dt.AsEnumerable()

                                                Where r.Item("PartNo").tostring = target
                                                Order By DirectCast(r.Item("Date"),DateTime) 
                                                Select r

    The part I can't figure out is, how to group the rows by "SerialNo" and select only the row with the latest "Date" from the group.

    Any help would be greatly appreciated.

    Tuesday, August 20, 2019 3:38 PM

Answers

  • Check this:

    Dim rows = From r In dt.AsEnumerable().Cast(Of DataRow)

                Order By r("Date")

                Group By PartNo = r("PartNo"), SerialNo = r("SerialNo") Into Group

                Select g = Group.Last()

                Order By g("PartNo"), g("SerialNo")

     

    • Edited by Viorel_MVP Tuesday, August 20, 2019 6:46 PM
    • Marked as answer by nmeek Tuesday, August 20, 2019 7:52 PM
    Tuesday, August 20, 2019 6:41 PM

All replies

  • Check this:

    Dim rows = From r In dt.AsEnumerable().Cast(Of DataRow)

                Order By r("Date")

                Group By PartNo = r("PartNo"), SerialNo = r("SerialNo") Into Group

                Select g = Group.Last()

                Order By g("PartNo"), g("SerialNo")

     

    • Edited by Viorel_MVP Tuesday, August 20, 2019 6:46 PM
    • Marked as answer by nmeek Tuesday, August 20, 2019 7:52 PM
    Tuesday, August 20, 2019 6:41 PM
  • Thanks for the response. That was just what I needed!

    I did add a where clause to select only rows with a specific "PartNo", and cast the "Date" to DateTime, as the source table has the dates stored as strings, resulting in incorrect order.

    Tuesday, August 20, 2019 8:37 PM