CODEWITHSUNDEEP
c#sqllinq
vincentsty
vincentsty

Reputation: 3231

leaderboard ranking using LINQ rank number and same point

I have the table as follow in my database:
User Table
UserID
Name
Point

What i intend to do:

| Rank |  Name |  Point  |
| 344  | UserA |  2000   |
| 345  | UserB |  1500   |
| 345  | UserC |  1500   |
| 347  | UserD |  1200   |
| 348  | UserE |  1000   |
| 349  | UserF |  1000   | <-- targeted user
| 350  | UserG |  900    |
| 350  | UserH |  800    |
| 352  | UserI |  700    |
| 353  | UserJ |  600    |

What i have tried:

int point = 1000;
var top6 = db.User.Where(p => p.Point >= point).OrderBy(p => p.Point).Take(6);
var bottom4 = db.User.Where(p => p.Point < point).OrderByDescending(p => p.Point).Take(4);

var leaderboard = top6.Union(bottom4).OrderByDescending(p => p.Point);

What is the outcome currently?

|  Name |  Point  |
| UserA |  2000   |
| UserB |  1500   |
| UserC |  1500   |
| UserD |  1200   |
| UserE |  1000   |
| UserF |  1000   | <-- targeted user
| UserG |  900    |
| UserH |  800    |
| UserI |  700    |
| UserJ |  600    |

What still unsolved?

What is the problem I having? - Do not know how to assign global ranking to the user. - Do not know how to resolve tie condition to the rank. when tie, the rank should be same

Upvotes: 0

Views: 1004

Answers (2)

Marco
Marco

Reputation: 23945

//EDIT: Linqpad Instant Share to reproduce it: http://share.linqpad.net/urtpc9.linq

I've gone a slightly different approach to shree.pat18. Instead of generating a list and zipping it, I've used the overloaded Select() method to generate an index, after grouping the results, to deal with Tie in points.

Code from LinqPad:

void Main()
{
    var players = new List<Player>()
    {
        new Player() {Name ="UserA", Points=2500},
        new Player() {Name ="UserB", Points=2400},
        new Player() {Name ="UserC", Points=2300},
        new Player() {Name ="UserD", Points=2200},
        new Player() {Name ="UserE", Points=2100},
        new Player() {Name ="UserF", Points=2000},
        new Player() {Name ="UserG", Points=1900},
        new Player() {Name ="UserH", Points=1800},
        new Player() {Name ="UserI", Points=1800},
        new Player() {Name ="UserJ", Points=1700},
        new Player() {Name ="UserK", Points=1600},
        new Player() {Name ="UserL", Points=1500},
        new Player() {Name ="UserN", Points=1300},
        new Player() {Name ="UserM", Points=1300},
    };

    var TargetPoints = 1000;

    var RankedPlayers =  players.OrderByDescending(p => p.Points)
                                .GroupBy (p => p.Points)
                                .Select ((grp, i) => new {
                                    Rank = i,
                                    Players = grp.OrderByDescending (g => g.Name)
                                })
                                .Dump();



}
public class Player
{
    public string Name { get; set; }
    public int Points { get; set; }
}

Output in LinqPad:

enter image description here

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

Try this:

   int point = 1000;          
   var ranks = Enumerable.Range(1,db.User.Count);
   var result = db.User.OrderByDescending(x => x.points).ThenBy(x => x.Name).Zip(ranks, (x,y) => new {x.Name, x.Point, rank = y});
   var top6= result.Where(x => x.Point>= point).OrderBy(x => x.Point).Take(6);            
   var bottom4 = result.Where(x => x.Point< point).OrderByDescending(x => x.Point).Take(4);
   var leaderboard = top6.Union(bottom4).OrderByDescending(x => x.Point).ThenBy(x => x.Name);

Demo

Start by generating an ordered list, which we will use as our global ranks. Zip it with the appropriately ordered list of users. The output of the zipping step is a collection of anonymous objects which include a rank field. Then, just use the returned collection along with your logic to get the top, bottom and combined results.

Upvotes: 0

Related Questions