LINQ to SQL group by with take

Question

I have a table that looks like this:

Id GroupId Value

and it has about 100 rows

How can I return the top 10 rows for value but with no duplicating GroupId?

Answer 1

Not sure if this translates to LINQ-to-SQL, but here's an idea from L2Obj

var query = (from foo in foos
                group foo by foo.GroupId into fg
                select fg.OrderByDescending(f => f.Value).First())
                .OrderByDescending(f => f.Value)
                .Take(10);

In english, it groups on the GroupId and then selects the Foo with the highest Value from each group, orders those, and then takes 10. If anything, you could get a concrete list of your objects from L2SQL and then perform the grouping in memory, should not be a performance/memory issue since you say there are only 100 rows.

For LINQ-to-SQL, you might try something like this

var sqlQuery = (from foo in foos
                join y in
                    (from f2 in foos
                        join x in
                            (from f1 in foos
                            group f1 by f1.GroupId into vg
                            select new { GroupId = vg.Key, MaxVal = vg.Max(f => f.Value) })
                            on f2.GroupId equals x.GroupId
                        where f2.Value == x.MaxVal
                        group f2 by f2.GroupId into mg
                        select new { GroupId = mg.Key, MinId = mg.Min(f => f.Id) })
                on foo.Id equals y.MinId
                orderby foo.Value descending
                select foo).Take(10);

This is based on a SQL query to perform the same operation

Select top 10 f.*
From Foos f
Inner Join 
(Select f.GroupID, min(f.Id) as MinId
From Foos f
Inner Join
(Select GroupId, Max(Value) as MaxVal
From Foos
Group By GroupId) x
on f.GroupId = x.GroupId 
and f.Value = x.MaxVal
Group By f.GroupId) y
on f.Id = y.MinId
order by f.Value desc

It basically performs two groupings. The first gets the max value for each group, the second gets the min ID for each record from each group that has the max value (in case 2 records in a group have the same value), and then selects the top 10 records.

Answer 2

This should do it:

var results = table
    .GroupBy(x => x.GroupId)
    .Select(x => new { Row = x, Value = x.Max(y => y.Value) })
    .OrderByDescending(x => x.Value)
    .Select(x => x.Row)
    .Take(10);

Edit: Modified to return the entire object.

Answer 3

This one will get the full row values (it's working for me with the sample data I show bellow):

static void Main(string[] args)
{ 
    Whatever one = new Whatever() {GroupId = 1, Id = 1, Value = 2};
    Whatever two = new Whatever() { GroupId = 1, Id = 2, Value = 8 };
    Whatever three = new Whatever() { GroupId = 2, Id = 3, Value = 16 };
    Whatever four = new Whatever() { GroupId = 2, Id = 4, Value = 7 };
    Whatever five = new Whatever() { GroupId = 3, Id = 5, Value = 21 };
    Whatever six = new Whatever() { GroupId = 3, Id = 6, Value = 12 };
    Whatever seven = new Whatever() { GroupId = 4, Id = 7, Value = 5 };
    Whatever eight = new Whatever() { GroupId = 5, Id = 8, Value = 17 };
    Whatever nine = new Whatever() { GroupId = 6, Id = 9, Value = 13 };
    Whatever ten = new Whatever() { GroupId = 7, Id = 10, Value = 44 };

    List<Whatever> list = new List<Whatever>();
    list.Add(one);
    list.Add(two);
    list.Add(three);
    list.Add(four);
    list.Add(five);
    list.Add(six);
    list.Add(seven);
    list.Add(eight);
    list.Add(nine);
    list.Add(ten);

    var results = (from w in list
                   group w by w.GroupId into g
                   select new { GroupId = g.Key,
                                Value = g.Max(w => w.Value),
                                Id = g.OrderBy(w=>w.Value).Last().Id }).
                   OrderByDescending(w=>w.Value).Take(5);

    foreach (var r in results)
    {
        Console.WriteLine("GroupId = {0},
                           Id = {1},
                           Value = {2}",
                           r.GroupId, r.Id,  r.Value);
    }

}

Output:

GroupId = 7, Id = 10, Value = 44
GroupId = 3, Id = 5, Value = 21
GroupId = 5, Id = 8, Value = 17
GroupId = 2, Id = 3, Value = 16
GroupId = 6, Id = 9, Value = 13