LINQ Query with grouping and ranking

Question

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 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

Is it intentional that your data sample has multiple rows with same Week and Player ? — driis, Jan 23 '11 at 17:17
Yes, that is the critical piece, although i know how to sum them easily enough — Mark Lawson, Jan 23 '11 at 17:56
what should the result be if two players has the same point for one week? — Tomas Jansson, Jan 23 '11 at 18:36
Would you mind adding what your schema looks like for the tables? The data that you provided and the query you have is too confusing to make any sense of it and thus harder to provide a good answer. — Jeff Mercado, Jan 23 '11 at 20:49

Jeff Mercado · Accepted Answer · 2011-01-24T09:49:58.133

3

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,
            };

edited Jan 24 '11 at 09:49

answered Jan 24 '11 at 04:30

Jeff Mercado

129,526
32
251
272

Thanks that is pretty much the answer I was looking for. It doesnt quite cope with the situation where two people have the same top score for a week, but I should be able to get there now you have shown me the basic structure. Interesting use of 'group 1'. I would never have thought of that! – Mark Lawson Jan 24 '11 at 18:04
@Mark: It should be able to handle duplicate winners per week. The first part of the query (up to the first group by) is used to determine what the winning scores are for the week. The rest (from join to the end) determines which players obtained the winning scores for each week, sorts them and obtains the counts. the `group 1` part was just due to the fact that we're just counting and not doing anything else with that. – Jeff Mercado Jan 24 '11 at 19:20

score 2 · Answer 2 · answered Jan 23 '11 at 19:54

2

Try:

p.Points.Any()

Instead of:

p.Points != null

answered Jan 23 '11 at 19:54

leppie

115,091
17
196
297

score 0 · Answer 3 · answered Mar 08 '13 at 13:03

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;
            }

score 0 · Answer 4 · answered Jan 23 '11 at 20:16

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());

score 0 · Answer 5 · answered Jan 23 '11 at 20:52

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};

LINQ Query with grouping and ranking

5 Answers5

Linked