CODEWITHSUNDEEP
c#linqlinq-to-sql
xkcd
xkcd

Reputation: 2590

Linq distinct & max

I have to query this table:

symbol    time
------    ----------
aaa       2013-04-18 09:10:28.000    
bbb       2013-04-18 09:10:27.000    
aaa       2013-04-18 09:10:27.000    
bbb       2013-04-18 09:10:26.000

I need one row for all distinct symbols having the biggest time value. How do I have to write my linq query?

Thanks in advance,

Upvotes: 14

Views: 10933

Answers (4)

ankur pancholi
ankur pancholi

Reputation: 31

u can use lambda expression with Linq -

var result= (from li in List.Distinct().OrderByDescending(x => x.time)
                       select li).FirstOrDefault();

Upvotes: 0

Aron
Aron

Reputation: 15772

I would actually use.

void Main()
{
    var set = new [] {
        new Foo{A = "aaa", B = 1},
        new Foo{A = "bbb", B = 2},
        new Foo{A = "aaa", B = 3},
        new Foo{A = "bbb", B = 4},
    };

    var result = from x in set
                group x.B by x.A into g
                select new {id = g.Key, biggest = g.Max()};

    Console.WriteLine(result);
}

Upvotes: 3

Pranay Rana
Pranay Rana

Reputation: 176896

try out this 

var q = MyTable.GroupBy(x => x.symbol )
               .Select(g => g.OrderByDescending(i => i.time).First());

or use max like this

 var data = from r in MyTable
                   group r by r.symbol into g
                   select new { name= g.Key, data= g.Max(a=>a.time) };

Upvotes: 9

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236228

Group rows by symbol and then select from each group item with max time (Table is your database table name from context):

from r in Table
group r by r.symbol into g
select g.OrderByDescending(x => x.time).First()

Same with method syntax:

Table.GroupBy(r => r.symbol)
     .Select(g => g.OrderByDescending(x => x.time).First());

Upvotes: 23

Related Questions