LINQ Using Max() to select a single row

Question

I'm using LINQ on an IQueryable returned from NHibernate and I need to select the row with the maximum value(s) in a couple of fields.

I've simplified the bit that I'm sticking on. I need to select the one row from my table with the maximum value in one field.

var table = new Table { new Row(id: 1, status: 10), new Row(id: 2, status: 20) }

from u in table
group u by 1 into g
where u.Status == g.Max(u => u.Status)
select u

This is incorrect but I can't work out the right form.

BTW, what I'm actually trying to achieve is approximately this:

var clientAddress = this.repository.GetAll()
    .GroupBy(a => a)
    .SelectMany(
            g =>
            g.Where(
                a =>
                a.Reference == clientReference && 
                a.Status == ClientStatus.Live && 
                a.AddressReference == g.Max(x => x.AddressReference) && 
                a.StartDate == g.Max(x => x.StartDate)))
    .SingleOrDefault();

I started with the above lambda but I've been using LINQPad to try and work out the syntax for selecting the Max().

UPDATE

Removing the GroupBy was key.

var all = this.repository.GetAll();

var address = all
            .Where(
                a =>
                a.Reference == clientReference && 
                a.Status == ClientStatus.Live && 
                a.StartDate == all.Max(x => x.StartDate) &&
                a.AddressReference == all.Max(x => x.AddressReference))
            .SingleOrDefault();

possible duplicate: http://stackoverflow.com/questions/1101841/linq-how-to-perform-max-on-a-property-of-all-objects-in-a-collection-and-ret — M.Babcock, Feb 02 '12 at 15:29
@M.Babcock there was a good answer quite far down in that question: http://stackoverflow.com/a/6330485/444244 — Boggin, Feb 02 '12 at 15:58
Take a look at [the answer](http://stackoverflow.com/a/1101979/490018). — Sergey Brunov, Feb 02 '12 at 17:27
@Serge I agree that [morelinq](http://code.google.com/p/morelinq/) would be best but I'm afraid this project has impediments to adding new libraries. — Boggin, Feb 02 '12 at 21:20
possible duplicate of [How to use LINQ to select object with minimum or maximum property value](http://stackoverflow.com/questions/914109/how-to-use-linq-to-select-object-with-minimum-or-maximum-property-value) — nawfal, Jul 19 '14 at 18:44

Daniel Hilgarth · Accepted Answer · 2013-07-05T13:08:56.143

248

I don't see why you are grouping here.

Try this:

var maxValue = table.Max(x => x.Status)
var result = table.First(x => x.Status == maxValue);

An alternate approach that would iterate table only once would be this:

var result = table.OrderByDescending(x => x.Status).First();

This is helpful if table is an IEnumerable<T> that is not present in memory or that is calculated on the fly.

edited Jul 05 '13 at 13:08

answered Feb 02 '12 at 15:30

Daniel Hilgarth

159,901
39
297
411

1

I took out the grouping and found I could get it working: `from u in User_Accounts where u.Status == User_Accounts.Max(y => y.Status) select u` – Boggin Feb 02 '12 at 16:05
1

You can also nest the lambda syntax: `table.First(x => x.Status == table.Max(x => x.Status))` – Landon Poch Aug 21 '12 at 22:15
15

@LandonPoch: That's not a good idea, as this would calculate the maximum N times with N being the number of items in `table`. – Daniel Hilgarth Aug 22 '12 at 04:06
2

@Daniel Hilgarth: Good catch! That would in fact calculate the max per every row in the table. My bad. – Landon Poch Aug 27 '12 at 03:31

score 18 · Answer 2 · edited Jun 15 '15 at 16:22

18

You can also do:

(from u in table
orderby u.Status descending
select u).Take(1);

edited Jun 15 '15 at 16:22

Chris Walsh

3,166
1
34
54

answered Aug 26 '14 at 14:48

KAPIL SHARMA

519
6
4

score 13 · Answer 3 · answered Feb 02 '12 at 15:28

You can group by status and select a row from the largest group:

table.GroupBy(r => r.Status).OrderByDescending(g => g.Key).First().First();

The first First() gets the first group (the set of rows with the largest status); the second First() gets the first row in that group.
If the status is always unqiue, you can replace the second First() with Single().

score 7 · Answer 4 · answered Nov 10 '15 at 19:38

Addressing the first question, if you need to take several rows grouped by certain criteria with the other column with max value you can do something like this:

var query =
    from u1 in table
    join u2 in (
        from u in table
        group u by u.GroupId into g
        select new { GroupId = g.Key, MaxStatus = g.Max(x => x.Status) }
    ) on new { u1.GroupId, u1.Status } equals new { u2.GroupId, Status = u2.MaxStatus}
    select u1;

score 0 · Answer 5 · edited Apr 02 '19 at 13:19

0

More one example:

Follow:

 qryAux = (from q in qryAux where
            q.OrdSeq == (from pp in Sessao.Query<NameTable>() where pp.FieldPk
            == q.FieldPk select pp.OrdSeq).Max() select q);

Equals:

 select t.*   from nametable t  where t.OrdSeq =
        (select max(t2.OrdSeq) from nametable t2 where t2.FieldPk= t.FieldPk)

edited Apr 02 '19 at 13:19

ALFA

1,705
1
8
18

answered Apr 02 '19 at 12:55

SantanaFire

101
2

score -1 · Answer 6 · answered Nov 13 '17 at 12:25

-1

Simply in one line:

var result = table.First(x => x.Status == table.Max(y => y.Status));

Notice that there are two action. the inner action is for finding the max value, the outer action is for get the desired object.

answered Nov 13 '17 at 12:25

Shneor

268
3
4

This method was discussed in the comments to the accepted answer where it was pointed out it was a bad idea. – Boggin Nov 13 '17 at 12:30
It is a bad idea because table.Max(y => y.Status) is going to be invoked once for each item in table, which is far from being optimal. – pablocom96 Feb 07 '21 at 09:53

LINQ Using Max() to select a single row

6 Answers6

Linked