none
Group By & Max() in LINQ RRS feed

  • Question

  •  

    I usually like to figure stuff out myself, but for some reason I just cant get this.  Here's what I'm trying to accomplish using LINQ:

     

    I've got a table that looks like this:

     

    ID     ProducerID      TypeID      AgentID       EffectiveDate      Rate

    1      37                   2              5                 1/1/2008            .025

    2      37                   2              5                 4/1/2008            .030

    3      11                   1              13               3/1/2008            .002

     

    I want my query to return ONLY the IDs of the "current" records.  Meaning, for each ProducerID/TypeID/AgentID combination, I want the ID of the most recent record using EffectiveDate.  In the example above, I would expect IDs 2 & 3 to be returned.  ID=2 is more recent than ID=1 and ID=3 is the only instance of that particular ProducerID/TypeID/AgentID combination. 

     

    I tried grouping ProducerID/TypeID/AgentID and doing a MAX() on the effective date to narrow it down, but for whatever reason I just am not getting it to work.  I'm having a hard enough time querying this in t-sql, let alone LINQ.

     

    Thanks in advance for any guidance!

    Monday, May 12, 2008 7:35 PM

Answers

  • Something along these lines will work:

     

    Code Snippet

        

     var query = from t in dc.MyTable
                 group t by new { a = t.producerID, b = t.agentID, c = t.typeID } into g
                 select g;

     

                 foreach (var x in query)
                 {
                     Console.WriteLine(x.Max(y => y.id));
                 }

     

     

     

    Hope that helps,

     

    --Samir

     

    Monday, May 12, 2008 9:23 PM

All replies

  • Something along these lines will work:

     

    Code Snippet

        

     var query = from t in dc.MyTable
                 group t by new { a = t.producerID, b = t.agentID, c = t.typeID } into g
                 select g;

     

                 foreach (var x in query)
                 {
                     Console.WriteLine(x.Max(y => y.id));
                 }

     

     

     

    Hope that helps,

     

    --Samir

     

    Monday, May 12, 2008 9:23 PM

  • Thanks, but that's not quite what I'm looking for.  I need to take effective date into account.  Theoretically, my table could look like this:

    ID     ProducerID      TypeID      AgentID       EffectiveDate      Rate

    2      37                   2              5                 1/1/2008            .025

    1      37                   2              5                 4/1/2008            .030

    3      11                   1              13               3/1/2008            .002


    In this case, I would want my result set to be IDs 1 & 3.  This is because ID=1 has a more recent effective date for the combination 37/2/5.  And ID=3 is the only record w/ a combination of 11/1/13.

    So taking the Max ID wouldn't be reliable.  I somehow need to group by ProducerID, TypeID, AgentID and for the Max(EffectiveDate) grab the ID.

    thanks,
    jason
    Tuesday, May 13, 2008 12:48 AM
  • Try the following:

     

    Code Snippet

    var query = from row in db.MyTable

    group row by new

    {

    row.ProducerID,

    row.TypeID,

    row.AgentID

    } into gr

    select gr.Single(result => result.EffectiveDate ==

    gr.Max(sub => sub.EffectiveDate)).ID;

     

     

    Tuesday, May 13, 2008 5:21 PM
  • See my reply here.

    Code Snippet

    from rate in rates
    group rate by new { rate.ProducerId, rate.AgentId, rate.TypeId } into grouping
    let value = grouping.OrderByDescending(r => r.EfectiveDate).First().RateValue
    select new { grouping.Key, Rate = value };

    Thursday, May 15, 2008 3:53 PM
  • Awsome!!!!!!!!!!!!It worked for me :)
    Sunday, August 11, 2019 10:55 AM