Select ID from DataTable group by Date RRS feed

  • Question

  • Hello!

    I have a datatable with ID and Date (columns ID and ProductionDate).

    I wan't to emphasise that I am not working with SQL table, but object DataTable in C#.

    Some IDs have same dates. For example:

    ID      ProductionDate

    1        1.1.2010

    2        1.1.2010

    3        1.1.2010 //largest ID with ProductionDate 1.1.2010

    4        3.2.2010

    5        8.3.2010

    6        8.3.2010 //largest ID with ProductionDate 8.3.2010


    What I have to do with this datatable is keep only dates, where ID is the largest. So, my output should be:

    ID            ProductionDate

    3              1.1.2010

    4              3.2.2010

    6              8.3.2010

     I guess SQL sentence for this would be:

     select max(ID), ProductionDate from table groupby ProductionDate

    -> my problem is that I don't know how to apply this with LINQ. Basically I need to create a new DataTable based on query. And idea how to solve this with LINQ?



    God bless, Frenky

    Thursday, May 13, 2010 7:18 AM


  • Hi Frenky,

    You can implement it like this:

    var results = from p in datatable.AsEnumerable()
           group p by p["ProductionDate"] into g
           select new
             ID = g.Max(u => u["ID"]),
             Date = g.Key
    this.dataGridView1.DataSource = results.ToList ();

    Best regards,
    Alex Liang

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Wednesday, May 19, 2010 10:12 AM
    Friday, May 14, 2010 6:43 AM