Linq query to select top records

Question

I have IEnumerable<MyData> which contains following data

Fruits | Name | Quantity | 
__________________________
 Mango | Jay  |    10    |
__________________________
 Apple | Jay  |    16    |
__________________________
 Grapes| Jay  |    12    |
__________________________
 Mango | Raj  |    11    |
__________________________
 Apple | Raj  |    20    |
__________________________
 Grapes| Raj  |    3     |
__________________________
 Mango | Vik  |    20    |
__________________________
 Apple | Vik  |    15    |
__________________________

I need to select from Linq top two quantity according to name like

Jay (10+16+12) = 38
Raj (11+20+3) = 34
Vik (20+15) = 35

Jay and Vik have top two quantity sum so I need these records

Fruits | Name | Quantity | 
__________________________
 Mango | Jay  |    10    |
__________________________
 Apple | Jay  |    16    |
__________________________
 Grapes| Jay  |    12    |
__________________________
 Mango | Vik  |    20    |
__________________________
 Apple | Vik  |    15    |
__________________________

So just to clarify. You want to sum the quantities per name, find the top two names, and then select the records with those names? — Ray, Dec 22 '11 at 11:16

score 12 · Accepted Answer · edited Dec 22 '11 at 11:29

12

Sounds like you might want something like:

var query = from item in collection
            group item by item.Name into g
            orderby g.Sum(x => x.Quantity)  descending
            select g;
var topTwo = query.Take(2);

That will take the first two groups, so you'd use it as:

foreach (var group in topTwo)
{
    Console.WriteLine("Name: {0}", group.Key);
    foreach (var item in group)
    {
        Console.WriteLine("  {0}: {1}", item.Fruits, item.Quantity);
    }
}

edited Dec 22 '11 at 11:29

Govind Malviya

13,627
17
68
94

answered Dec 22 '11 at 11:19

Jon Skeet

1,421,763
867
9,128
9,194

1

Should that not be orderby desc? – Ray Dec 22 '11 at 11:26
But you may wish to find the top n results without performing a complete sort on a (potentially much larger) set of m results. Is there any way for Linq to do this? Thanks, PW – Phil Whittington Sep 26 '13 at 12:51
Ah - I suppose with Lazy-Linq, this is fine - I probably need to remove a few .ToArray()'s elsewhere in my code... – Phil Whittington Sep 26 '13 at 12:52
2

@PhilWhittington: In LINQ to Objects? Not really - I have an alternative LINQ to Objects implementation which uses a "just-in-time" quicksort, making this sort of thing more efficient than the MS implementation (which *does* do a complete sort before returning anything). In LINQ to SQL etc, it would be up to the database anyway. – Jon Skeet Sep 26 '13 at 12:53
Thank you @JonSkeet- I wasn't aware of that. I once used your MinBy (delegate) Linq extension (http://stackoverflow.com/questions/914109/how-to-use-linq-to-select-object-with-minimum-or-maximum-property-value) a long time ago. I will search for your full Linq alternative; it may be useful as we have a notion of Rank() for search results, but we don't want to sort any but the top 30. Many thanks. – Phil Whittington Sep 26 '13 at 13:25
1

@PhilWhittington: See http://msmvps.com/blogs/jon_skeet/archive/2011/01/07/reimplementing-linq-to-objects-part-26d-fixing-the-key-selectors-and-yielding-early.aspx – Jon Skeet Sep 26 '13 at 13:28

score 4 · Answer 2 · edited Dec 23 '11 at 05:39

Something like this would work.

private static IEnumerable<MyData> GetTop2Names(IEnumerable<MyData> data)
{
    var top2 = data.GroupBy(d => d.Name)
                   .OrderByDescending(g => g.Sum(d => d.Quantity))
                   .Take(2)
                   .Select(g => g.Key);
    return data.Where(d => top2.Contains(d.Name));
}

Step by step

Group by Name (as that's what you're summing)
Sort by the sum of the quantities
Take the top 2 names
Select the items from the original list that match those names.

score 0 · Answer 3 · edited Dec 23 '11 at 05:40

0

Try the following:

var topTwo = myData.GroupBy(d => d.Name).OrderByDescending(g => g.Sum(d => d.Quantity)).TakeWhile((data,index) => index < 2).SelectMany(g => g);

edited Dec 23 '11 at 05:40

Govind Malviya

13,627
17
68
94

answered Dec 22 '11 at 11:18

Rich O'Kelly

41,274
9
83
114

score 0 · Answer 4 · edited Dec 23 '13 at 14:23

0

Should look like this:

IEnumerable<MyData> source = new List<MyData>();
var names = source
    .GroupBy(item => item.Name)
    .ToDictionary(item => item.Key, item => item.Sum(i => i.Quantity))
    .OrderByDescending(item => item.Value)
    .Select(item => item.Key)
    .Take(2);

var result = source.Where(item => names.Contains(item.Name));

edited Dec 23 '13 at 14:23

Govind Malviya

13,627
17
68
94

answered Dec 22 '11 at 11:23

Sergei B.

3,227
19
18

Linq query to select top records

4 Answers4