Max from each group LINQ

I have tried every possible way and looked up everywhere. I am in frustration because this is really silly. I want max from each group. I have below records.

ID  Custno  paydt                    rcvDt                  Mon    Tue
17  106505  2009-12-31 00:00:00.000 2012-07-11 07:15:04.523 100     3
18  106505  2009-12-31 00:00:00.000 2012-07-11 07:15:04.543 NULL    NULL
19  106505  2010-12-31 00:00:00.000 2012-07-11 07:15:04.747 3       3
20  106505  2011-12-31 00:00:00.000 2012-07-11 07:15:04.827 3       3

result should be group by Custno and paydt and return with max id

ID  Custno  paydt                    rcvDt                  Mon    Tue
18  106505  2009-12-31 00:00:00.000 2012-07-11 07:15:04.543 NULL    NULL
19  106505  2010-12-31 00:00:00.000 2012-07-11 07:15:04.747 3   3
20  106505  2011-12-31 00:00:00.000 2012-07-11 07:15:04.827 3   3

Tried different way and one of them is below

var q = from x in MyTable.ToList()
                        group x by new { x.custno, x.paydt } into g
                        select new MyTable
                        {
                            custno= g.Key.custno,
                            paydt= g.Key.paydt,
                            ID = g.Max(b => b.ID),
                            rcvDt = g.OrderByDescending(a => a.ID).First().rcvDt ,
                            mon = g.OrderByDescending(a => a.ID).First().mon,
                            tue = g.OrderByDescending(a => a.ID).First().tue
                        };

will appreciate help!

Answers


You can use:

var q = MyTable.GroupBy(x => new {x.custno, x.paydt})
               .Select(g => g.OrderByDescending(i => i.ID).First());

This groups using your same logic. It performs the same grouping you were using, but then selects, from within each group, the first record after sorting the group by ID descending.


try

var q = MyTable.GroupBy(x => new { x.custno, x.paydt }).Max(y=> y.Max(z => z.ID));

it should work.


Not sure if its possible to do in a single query, but you always can do it in two steps:

    var maxIds = MyTable.GroupBy(x => new {x.custno, x.paydt}).Select(g => g.Max(i => i.ID));
    var result = MyTable.Where(x => maxIds.Contains(x.ID));

Need Your Help

Spring Security OAuth (separate client on angular2)

java spring oauth spring-security angular

I have a problem with configuring spring security and oauth2.

how can meteor be coupled to an android app?

android meteor

The meteor homepage (www.meteor.com) says in bullet point 8 ("interoperability"):