How do I get the MAX row with a GROUP BY in LINQ query?

Question

I am looking for a way in LINQ to match the follow SQL Query.

Select max(uid) as uid, Serial_Number from Table Group BY Serial_Number

Really looking for some help on this one. The above query gets the max uid of each Serial Number because of the Group By Syntax.

score 101 · Accepted Answer · answered Oct 01 '08 at 14:34

101

        using (DataContext dc = new DataContext())
        {
            var q = from t in dc.TableTests
                    group t by t.SerialNumber
                        into g
                        select new
                        {
                            SerialNumber = g.Key,
                            uid = (from t2 in g select t2.uid).Max()
                        };
        }

answered Oct 01 '08 at 14:34

tvanfosson

524,688
99
697
795

1

I want to accept both as answers, but I guess I can't so I voted for both of you guys. Thanks a BUNCH!!! – SpoiledTechie.com Oct 01 '08 at 16:33

DamienG · Answer 2 · 2012-02-08T17:01:57.583

74

var q = from s in db.Serials
        group s by s.Serial_Number into g
        select new {Serial_Number = g.Key, MaxUid = g.Max(s => s.uid) }

edited Feb 08 '12 at 17:01

answered Oct 01 '08 at 14:37

DamienG

6,575
27
43

I want to accept both as answers, but I guess I can't so I voted for both of you guys. Thanks a BUNCH!!! – SpoiledTechie.com Oct 01 '08 at 15:08
Also note that the variable for the => lambda expression in the Max function can be anything (s => s.uid, tv => tv.uid, asdf => asdf.uid). Linq will automatically recognize it as selecting over elements of type Serial. – Michael Mar 17 '11 at 17:03

score 42 · Answer 3 · answered Aug 21 '13 at 17:48

42

In methods chain form:

db.Serials.GroupBy(i => i.Serial_Number).Select(g => new
    {
        Serial_Number = g.Key,
        uid = g.Max(row => row.uid)
    });

answered Aug 21 '13 at 17:48

Ilya Serbis

21,149
6
87
74

score 24 · Answer 4 · answered Jul 24 '10 at 12:24

24

I've checked DamienG's answer in LinqPad. Instead of

g.Group.Max(s => s.uid)

should be

g.Max(s => s.uid)

Thank you!

answered Jul 24 '10 at 12:24

denis_n

783
8
29

15

This should probably a comment on DamienG's answer. – Cᴏʀʏ Mar 07 '12 at 21:40

score 14 · Answer 5 · answered Feb 24 '15 at 12:50

14

The answers are OK if you only require those two fields, but for a more complex object, maybe this approach could be useful:

from x in db.Serials 
group x by x.Serial_Number into g 
orderby g.Key 
select g.OrderByDescending(z => z.uid)
.FirstOrDefault()

... this will avoid the "select new"

answered Feb 24 '15 at 12:50

Javier

2,093
35
50

I like this - do you know if it is as efficient as the other answers? – noelicus Aug 25 '16 at 07:57
You used the query syntax here all the way up to the end, where you switched to method syntax. Did you have to? Is the a full query syntax form for your solution? – Seth Dec 30 '16 at 22:59

score 6 · Answer 6 · answered Jul 16 '20 at 06:59

6

This can be done using GroupBy and SelectMany in LINQ lamda expression

var groupByMax = list.GroupBy(x=>x.item1).SelectMany(y=>y.Where(z=>z.item2 == y.Max(i=>i.item2)));

answered Jul 16 '20 at 06:59

Abhas Bhoi

349
3
3

score 0 · Answer 7 · answered Aug 04 '22 at 04:35

Building upon the above, I wanted to get the best result in each group into a list of the same type as the original list:

    var bests = from x in origRecords
            group x by x.EventDescriptionGenderView into g
            orderby g.Key
            select g.OrderByDescending(z => z.AgeGrade)
            .FirstOrDefault();

    List<MasterRecordResultClaim> records = new 
                  List<MasterRecordResultClaim>();
    foreach (var bestresult in bests)
    {
        records.Add(bestresult);
    }

EventDescriptionGenderView is a meld of several fields into a string. This picks the best AgeGrade for each event.

How do I get the MAX row with a GROUP BY in LINQ query?

7 Answers7

Linked

Related