C#, Linq2SQL: Getting the highest of each group.

Question

Lets say I have this table:

PetID    Name    Age    Weight

How can I in a Linq2SQL get the name, age and weight of each pet that is the heaviest in its age group?

So if I have one pet of age 5 and four pets of age 2, I would like the Name, Age and Weight of the one that is age 5, and the one of the four with age 2 that is the heaveiest of those four.

This should probably not be too difficult, but I am not too into query thinking yet. Working on it though. And this is one kind of problem that I will need many times, but doesn't quite manage to wrap my head around how to do...

I think it would include using the Max or a > operator, and some sort of grouping or joining, but thats about as far as I've gotten....

---

Thanks for the answers. All of them put me on the right track when it comes to the GroupBy stuff. The one I marked as an answer, was the one that was closest to what I ended up with.

Answer 1

Try this query:

var petsGroup = ctx.Pets.GroupBy(p => p.Age)
                        .Select(g => g.First(x => x.Weight == g.Max(y => y.Weight)));

It groups the Pets by Age, and then selects the first element on the group, that matchs with the maximum weight of the group.

Answer 2

how is this for you.

public class Pet
{
     public int PetID { get; set; }
     public string Name { get; set; }
     public int Age { get; set; }
     public decimal Weight { get; set; }
}

List<Pet> pets = new List<Pet>() 
{ 
    new Pet {PetID = 1, Age = 5, Name = "Bob", Weight = 4M },
    new Pet {PetID = 2, Age = 5, Name = "Brad", Weight = 3M },
    new Pet {PetID = 3, Age = 2, Name = "Troy", Weight = 1M },
    new Pet {PetID = 4, Age = 2, Name = "Dave", Weight = 2M },
    new Pet {PetID = 5, Age = 2, Name = "Simon", Weight = 3M }
};

var fatPetsByAge = from pet in pets
                   group pet by pet.Age into petsByAge
                   select new
                   {
                       Age = petsByAge.Key,
                       FatPet = petsByAge.FirstOrDefault(
                                f => f.Weight == petsByAge.Max(m => m.Weight))
                   };

foreach (var fatty in fatPetsByAge)
{
    Console.WriteLine("PetID: {0}, Age: {1} Name: {2} Weight: {3}",
        fatty.FatPet.PetID,
        fatty.FatPet.Age,
        fatty.FatPet.Name,
        fatty.FatPet.Weight);
}

Answer 3

It could be something like this:

var pets =  from pet in petSource
            group pet by pet.Age into g
            let max = g.OrderByDescending(p => p.Weight).FirstOrDefault<Pet>()
            select new { Name = max.Name, Age = max.Age, Weight = max.Weight };