none
Rank with Linq RRS feed

  • Question

  • Suppose I have a sql table of orders that looks something like this:-

     

    Salesman, OrderID, OrderValue

    Andrew,1,100

    Andrew,2,200

    Brian,3,10

    Brian,4,2000

    Brian,5,1000

     

    I'm trying to create a linq query to show the current rank of an individual (based on total order value). In the example above I want to supply "Brian" and get a return value of 1 (indicating he is ranked 1 in terms of total sales). I'm really struggling to work out the Linq syntax. Can anyone point me in the right direction?

     

    Thanks

     

    Andrew

    Thursday, August 14, 2008 8:42 PM

All replies

  • I'm sure there are multiple ways to do this -- here is one:

     

    Code Snippet

    var q = from s in dc.salesmen
                 orderby s.sales descending
                 select new {
                     Name = s.name,
                     Rank = (from o in dc.salesmen
                             where o.sales > s.sales
                             select o).Count() + 1
                 };

     

     

     

     

    Thanks,

     

    --Samir

     

    Friday, August 15, 2008 7:49 PM
  • Or even

    Code Snippet

    var index =1;

    var q = from s in data

    orderby s.sales descending

    select new

    {

    Name = s.name,

    Rank = index++

    };

     

     

    However Samir's is better as it will return the correct rank even if paged.

    Tuesday, August 19, 2008 8:08 AM
    Answerer
  • Sorry I did not make my question clear. You rank the salesmen by total sales not highest individual sale. I have a solution that basically does the Linq query to get salesmen totals in order, I then use ToList() and a for next loop to find the salesman I'm looking for. I'm sure it could be done in one Linq query.

     

    I suppose my question comes down to "can you use the SQL ROW_NUMBER() function in Linq?"

     

    Andrew

    Tuesday, August 19, 2008 8:50 AM
  • No problem, all is clear. Like a lot of things, it's easier to break down into smaller problems and fix each one in turn.

     

    First, get salesman and total sales, then work out their position in the list

     

    Code Snippet

    // build query listing sales without position

    var query1 = from s in db.Orders

                 group s by s.Salesman

                 into g

                 orderby g.Sum(o => o.OrderValue) descending

       select new

       {

    Name = g.Key,

    TotalSales = g.Sum(o => o.OrderValue)
    };

     

    // build second query to add position

    var query2 = from s in query1

    select new

    {

    Name = s.Name,

    TotalSales = s.TotalSales,

    Position = query1.Count(s2 => s2.TotalSales > s.TotalSales)+1

    };

     

     

     

    Tuesday, August 19, 2008 3:32 PM
    Answerer
  • Hi!

     

    Did you see this before?

    http://smehrozalam.wordpress.com/tag/ranking-functions/

    Wednesday, August 17, 2011 7:36 PM
  • Quite Userful
    Tuesday, December 29, 2015 3:51 AM
  • I've spent 2 days looking for an elegant solution to this. Thank you so much.
    Tuesday, September 19, 2017 4:43 AM