LINQ Query with grouping and ranking

I have a sql table called predictions with data as below

Week    Player     Points
201101  Mark       7
201101  Mark       7
201101  Pete       7
201101  Pete       3
201101  Mark       5
201102  Mark       2
201102  Mark       3
201102  Pete       7
201102  Pete       5
201102  Mark       5
201103  Mark       7
201103  Mark       7
201103  Pete       7
201103  Pete       3
201103  Mark       5

Each row in the table represents a football match, hence several per week

The result I need is

Player    Count of Weekly wins
Mark      2
Pete      1

So Mark had the most points in week 2011011 and 201103, Pete had the most points in week 201102

Getting the total number of points per player per week is easy. But I cannot work out how to take the highest from that weekly group and get to the result I need.

I have this all in sql query using the RANK function and then selecting all the players who have a rank of 1. This is good because if two players have the same score for a week they both get counted correctly. But I want a LINQ开发者_StackOverflow version because it is cool and fits my reworking of a web site!

Any help would be appreciated.

OK I have got this far which is summing the data for each player for each week. What i now need to do is pick the top entry for each week and count it against the player

(from p in FootballPredictions
        where p.FootballMatches.WeekNum <= 201101 && p.Points != null
        group p by new { p.FootballMatches.WeekNum, p.Player } into g
        orderby g.Key.WeekNum, g.Sum(p => p.Points) descending
        select new
        {
            WeekNum = g.Key.WeekNum,
            Player = g.Key.Player,
            Points = g.Sum(p => p.Points),
        })

Giving

WeekNum Player           Points 
201033  ranteld           26           <--- 1 point
201033  nicolap           25
201033  Mark              25
201033  1969 cup winners  25
201033  footysi           24
201033  Brendan           22
201033  monty             22
201033  Sandra Phillips   21
201033  SPB               20
201033  John Poulton      20
201033  RobBrown          19
201033  Steve Gardner     17
201033  Nick              16
201033  MikeSpeke         15
201034  Sandra Phillips   32           <--- 1 point
201034  Steve Gardner     27
201034  ranteld           25
201034  John Poulton      23
201034  footysi           23
201034  Mark              17
201034  nicolap           13
201034  Brendan           13
201035  Brendan           34           <--- 1 point
201035  Sandra Phillips   34           <--- 1 point
201035  nicolap           31
201035  1969 cup winners  25
201035  MikeSpeke         24
201035  Steve Gardner     22
201035  Mark              20
201035  ranteld           20
201035  Football Freddie  16

So the real answer from this table is

Player             Wins
Sandra Philips      2
Brendan             1
ranteld             1

Hope that clarifies

It was somewhat confusing to see that your query didn't seem to correspond to the data. So instead, this will be based on the data alone. The query should produce valid SQL so you won't have to use LINQ to Objects. You can adapt it to your tables with little modification.

var query = from pred in Predictions
            group pred.Points by pred.WeekNum into week
            join pred in Predictions
                on new { WeekNum = week.Key, Points = week.Max() }
                equals new { pred.WeekNum, pred.Points }
            group 1 by pred.Player into player
            let Wins = player.Count()
            orderby Wins descending, player.Key
            select new
            {
                Player = player.Key,
                Wins,
            };

Try:

p.Points.Any()

Instead of:

p.Points != null

This code seems to be what you need:

    var result = this.DataList
            .GroupBy(data => data.Week)
            .Select(data=>
            {
                return data.GroupBy(item => item.Name)
                    .Select(item => new { Name = item.Key, SumPoints = item.Sum(v => v.Points) })
                    .OrderBy(item => item.SumPoints)
                    .FirstOrDefault();
            })
            .GroupBy(_=>_.Name)
            .ToDictionary(_=>_.Key, _=>_.Count());

mine is quite long

var weeklyTopScore = from eachMatch in FootballPredictions
                     group eachMatch by eachMatch.week
                     into weekly
                     select new {week = weekly.Key, topScore = weekly.Max(match => match.points)};

var playerWins = from eachResult in weeklyTopScore
                 join entry in FootballPredictions
                 on eachResult.week equals entry.week
                 where eachResult.topScore == entry.points
                 group entry by entry.player
                 into winner
                 select new { player = winner.Key, wins = winner.Count() };

var result = from entry in playerWins
             group entry by entry.wins
             into summary
             select new { player = summary.Select(data => data.player).Aggregate((cur, nex) => cur + ", " + nex), wins = summary.Key};

Just pass a datatable to following function (Please note that the code is in c#) //dt should contain column points but not rank column

 public static DataTable GetRankedDatatable(DataTable dt)
            {
                var rankedDt = (from row in dt.AsEnumerable()
                                orderby row.Field<string>("points")
                                select row).CopyToDataTable();
                rankedDt.Columns.Add("rank");
                int rank = 0;
                for (int i = 0; i < rankedDt.Rows.Count - 1; i++)
                {
                    rankedDt.Rows[i]["rank"] = rank;
                    if (rankedDt.Rows[i]["points"].ToString() != rankedDt.Rows[i + 1]["points"].ToString())
                    {
                        rank++;
                    }
                }
                rankedDt.Rows[rankedDt.Rows.Count - 1]["rank"] = rank;
                return rankedDt;
            }