16

Summary: How to get top 1 element in ordered groups of data

I am trying to group by a CarId field , and then within each group, I want to sort on a DateTimeStamp field descending. The desired data would be for each Car give me the latest DateTimeStamp and only that 1 in the group.

I can get to this point, but having problems taking the top 1 off of the group and ordering the group by DateTimeStamp desc.

Here is what I have after the first group operation:

group 1
------------------------
CarId  DateTimeStamp 
1      1/1/2010
1      1/3/2010
1      3/4/2010

group 2
------------------------
CarId  DateTimeStamp 
2      10/1/2009
2      1/3/2010
2      9/4/2010

what I would desire only the top 1 in an ordered group

    group 1
    ------------------------
    CarId  DateTimeStamp 
    1      3/4/2010

    group 2
    ------------------------
    CarId  DateTimeStamp 
    2      9/4/2010

Brickwall: Where I get stopped, is needing the CarId and DateTimeStamp in the group by clause, in order to later sort by the DateTimeStamp. Maybe the sorting of the date should be done in a separate function, not sure.

RyBolt
  • 1,764
  • 3
  • 22
  • 34

2 Answers2

20
data
    .GroupBy(
        x => x.CardId, 
        (x, y) => new { 
            Key = x, 
            Value = y.OrderByDescending(z => z.DateTimeStamp).FirstOrDefault() 
        }
    );

This will group all the elements by CardId, then order the elements of each group by DateTimeStamp (descending), then pare down each group to only contain the first element. Finally, it returns an enumerable of "groups" (with the scare quotes since they're actually an anonymous type instead of an IGrouping) where each group has the one item you're seeking.

Kirk Woll
  • 76,112
  • 22
  • 180
  • 195
  • 5
    That did it! I did get an exception and had to use FirstOrDefault() instead of First(). Thanks! – RyBolt Oct 03 '10 at 17:38
  • +1: Perfect. Just what I needed (a version of grouping and first record I can actually follow :) – iCollect.it Ltd Jun 17 '11 at 08:18
  • thank you, i was searching for this, i searched `sql group by last record` but didn't find solution `linq group by last record` helped to come here, only need is ,i have to make it 'typed' for `ViewModel` – Shaiju T Jan 23 '16 at 16:33
  • Can I concatenate two columns in the orderbydescending? Like `z.Field("Date") + z.Field("Time")`? Since they are string and separate columns, how can I achieve that? How can I select multiple column beside the `CardId`? – Si8 Jun 28 '17 at 14:16
  • 1
    @Si8, you should really ask a separate question, but what you want is `.OrderByDescending(...).ThenByDescending(...)` – Kirk Woll Jun 28 '17 at 14:46
  • You are absolutely right and i did. I forgot to mention it here. Thanks. https://stackoverflow.com/questions/44764687/how-to-use-linq-to-group-by-and-order-by-certain-column – Si8 Jun 28 '17 at 14:52
2

I find the query expression syntax easier to understand myself:

from x in data 
group x by x.CarId into grp
select new {
    CarId = x.CarId,
    Record = (from x in grp orderby z.DateTimeStamp descending).FirstOrDefault()
};
user2395716
  • 21
  • 2