Datatable linq - Get the first row with maximum sum values

Question

I have a datatable (with 2 columns):

X Y

AA 100

BB 150

AA 25

BB 10

CC 120

As you can see for :

AA values the sum is 125

BB values the sum is 160

CC values the sum is 120

Them maximum sum is 160 for BB

I want to obtains this values, the maximum of sums, where sum is that of a grup, grouped on column X value, and also that X value.

How can I do this with LINQ in VB.NET ?

Thank you !

EDIT: This is the code that I'm using:

Dim marfa = _
                        From dr In dtMarfuriT _
                        Group dr By Key = dr("MRF_COD") Into Group _
                        Let maxim = Group.Sum(Function(dr) dr("MRF_MASA_STABILITA")) _
                        Select New With { _
                             Key .cod = Key, _
                             Key .TopMarfa = Group.First(Function(dr) Group.Sum(Function(drx) CDbl(drx("MRF_MASA_STABILITA"))) = maxim)("MRF_COD"), _
                             Key .Maximul = maxim}


                Dim MasaMaxima As Double = marfa.Maximul 

                Dim CodulMaseiMaxime As String = marfa.TopMarfa

did you try using .GroupBy(), .Sum(), .OrderByDescending() and .Take()? — LINQ2Vodka, Nov 12 '13 at 11:40
I see that. Did you try LINQ by youself? What issue did you meet? — LINQ2Vodka, Nov 12 '13 at 11:42

score 2 · Answer 1 · answered Nov 12 '13 at 11:56

B a1 = new B() { Text = "AA", Number = 100};
B a2 = new B() { Text = "BB", Number = 150};
B a3 = new B() { Text = "AA", Number = 25};
B a4 = new B() { Text = "BB", Number = 10};
B a5 = new B() { Text = "CC", Number = 120};

List<B> list = new List<B>();
list.Add(a1);
list.Add(a2);
list.Add(a3);
list.Add(a4);
list.Add(a5);

var l = from i in list
    group i by i.Text into g
    let sum = g.Sum(e=>e.Number)
    orderby sum descending 
    select new
    {
        Text = g.Key,
        Sum = sum
    };

var item = l.First();

Console.WriteLine(item.Sum);



public class B
{
    public int Number{get;set;}
    public string Text{get;set;}
}

Thank you ! Your code is good for me, too, but I can accept only one code. The both are good but unfinished so I put a finished one as soon as the site allow this (after 8 hours). — Tratak, Nov 12 '13 at 12:28

LINQ2Vodka · Accepted Answer · 2013-11-12T12:03:31.833

0

This is sample in C#

    // sample array
    Tuple<string, int>[] arr = new Tuple<string, int>[]
        {
            new Tuple<string, int>("AA", 10),
            new Tuple<string, int>("AA", 20),
            new Tuple<string, int>("BB", 10),
            new Tuple<string, int>("BB", 60)
        };

    // calcs
    var result = arr.GroupBy(a => a.Item1)
       .Select(g => new {Key = g.Key, S = g.Select(s => s.Item2).Sum()})
       .OrderByDescending(g => g.S)
       .FirstOrDefault();

    // or
    var result1 =
        (from tuple in arr
         group tuple by tuple.Item1 into gr
         let s = gr.Select(s => s.Item2).Sum()
         orderby s descending
         select new {x = gr, y = s}).FirstOrDefault();

edited Nov 12 '13 at 12:03

answered Nov 12 '13 at 11:54

LINQ2Vodka

2,996
2
27
47

Thx for OrderByDescending and FirstOrDefault ! I'm trying to translate your code in vb with datatable in place of tuple. – Tratak Nov 12 '13 at 12:02
@Tratak added a sample with SQL-like syntax. Think of Tuples as of any value pairs, you can use object or anything else – LINQ2Vodka Nov 12 '13 at 12:04
I know. I'll put the code translated and with datatable as quick as I can. – Tratak Nov 12 '13 at 12:09

score 0 · Answer 3 · answered Nov 14 '13 at 08:25

Dim result= _
     (From dr In dtX _
     Group dr By Key = dr("A") Into Group _
     Select New With { _
            Key .Code = Key, _
            SumX = Group.Select(Function(drx) CDbl(drx("B"))).SumX}) _
    .OrderByDescending(Function(g) g.SumX).FirstOrDefault

Dim MaxSumX As Double = result.SumX
Dim MaxCode As String = result.Code

Datatable linq - Get the first row with maximum sum values

3 Answers3