CODEWITHSUNDEEP
linq
Hussain
Hussain

Reputation: 223

Linq select top 1 name of a highest score group

I have a list which is like below

Name Score Date
S1   2     15/02/2013
S1   4     15/02/2013
S2   0     15/02/2013
My desired out should be
S1   6     15/02/2013

for this i wrote LINQ code as

(from _data in _rawData
    group _data by new { _data.Date, _data.Name } into gp orderby gp.Key.Date             
             select new
             {                 
                 score = gp.Sum(_score => _score.Score),
                 store = gp.Max(_store => _store.Name),
                 Date = gp.Max(_Date => _Date.Date)
             }).OrderByDescending(_score => _score.score)
           .ToList().
           ForEach(item => _timeLiner.Add(new TimeLiner()
           {
               id = _id++.ToString(),
               start = item.auditDate.ToString(),
               title = "TopStore: " + item.store
           }

I tried with .Take(1).ToList(), Single(), SingleOrDefault(), First() but i failed to get desired output. Instead i am getting as

S1    6     15/02/2013
S2    0     15/02/2013

Upvotes: 1

Views: 1404

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236288

top is a new range variable, which holds summary data for top score name of a day

from d in _rawData
group d by d.Date into dateGroup
let top = dateGroup.GroupBy(x => x.Name)
                   .Select(g => new { Name = g.Key, Score = g.Sum(x => x.Score)})
                   .OrderByDescending(x => x.Score)
                   .First()
select new
{                 
     Name = top.Name,
     Score = top.Score,
     Date = dateGroup.Key
}

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174427

The following should give you the desired output:

(from _data in _rawData
 group _data by new { _data.Date, _data.Name } into gp orderby gp.Key.Date
 select new
 {                 
     score = gp.Sum(_score => _score.Score),
     store = gp.Max(_store => _store.Name),
     Date = gp.Max(_Date => _Date.Date)
 }).OrderByDescending(_score => _score.score)
   .First();

Or, a little bit more readable:

_rawData.GroupBy(x => new { x.Date, x.Name })
        .Select(g => new {
                             Score = g.Sum(x => x.Score),
                             Store = g.Key.Name,
                             Date = g.Key.Date
                         }
        .OrderByDescending(x => x.Score)
        .First()

Upvotes: 0

Related Questions