CODEWITHSUNDEEP
c#linqgroup-bysql-order-by
Rita
Rita

Reputation: 1477

Linq order by, group by and order by each group?

I have an object that looks something like this:

public class Student
{
    public string Name { get; set; } 
    public int Grade { get; set; }
}

I would like to create the following query: group grades by student name, order each student group by grades, and order groups by max grade in each group.

So the result should look like this:

John 100
John 80
Lucy 80
Lucy 50
Lucy 40
Eric 70
Eric 30

I created the following query:

StudentsGrades.GroupBy(student => student.Name)
    .OrderBy(studentGradesGroup => studentGradesGroup.Max(student => student.Grade));

But that returns IEnumerable IGrouping, and I have no way to sort the list inside, unless I do that in another foreach query and add the results to a different list using AddRange.

Is there a prettier way to do that?

Upvotes: 87

Views: 201692

Answers (4)

Jon Skeet
Jon Skeet

Reputation: 1499860

Sure:

var query = grades.GroupBy(student => student.Name)
                  .Select(group => 
                        new { Name = group.Key,
                              Students = group.OrderByDescending(x => x.Grade) })
                  .OrderByDescending(group => group.Students.First().Grade);

Note that you can get away with just taking the first grade within each group after ordering, because you already know the first entry will have the highest grade.

Then you could display them with:

foreach (var group in query)
{
    Console.WriteLine("Group: {0}", group.Name);
    foreach (var student in group.Students)
    {
        Console.WriteLine("  {0}", student.Grade);
    }
}

Upvotes: 168

Sawyer
Sawyer

Reputation: 574

The way to do it without projection:

StudentsGrades.OrderBy(student => student.Name).
ThenBy(student => student.Grade);

Upvotes: 21

Bruno Torres
Bruno Torres

Reputation: 69

try this...

public class Student 
    {
        public int Grade { get; set; }
        public string Name { get; set; }
        public override string ToString()
        {
            return string.Format("Name{0} : Grade{1}", Name, Grade);
        }
    }

class Program
{
    static void Main(string[] args)
    {

      List<Student> listStudents = new List<Student>();
      listStudents.Add(new Student() { Grade = 10, Name = "Pedro" });
      listStudents.Add(new Student() { Grade = 10, Name = "Luana" });
      listStudents.Add(new Student() { Grade = 10, Name = "Maria" });
      listStudents.Add(new Student() { Grade = 11, Name = "Mario" });
      listStudents.Add(new Student() { Grade = 15, Name = "Mario" });
      listStudents.Add(new Student() { Grade = 10, Name = "Bruno" });
      listStudents.Add(new Student() { Grade = 10, Name = "Luana" });
      listStudents.Add(new Student() { Grade = 11, Name = "Luana" });
      listStudents.Add(new Student() { Grade = 22, Name = "Maria" });
      listStudents.Add(new Student() { Grade = 55, Name = "Bruno" });
      listStudents.Add(new Student() { Grade = 77, Name = "Maria" });
      listStudents.Add(new Student() { Grade = 66, Name = "Maria" });
      listStudents.Add(new Student() { Grade = 88, Name = "Bruno" });
      listStudents.Add(new Student() { Grade = 42, Name = "Pedro" });
      listStudents.Add(new Student() { Grade = 33, Name = "Bruno" });
      listStudents.Add(new Student() { Grade = 33, Name = "Luciana" });
      listStudents.Add(new Student() { Grade = 17, Name = "Maria" });
      listStudents.Add(new Student() { Grade = 25, Name = "Luana" });
      listStudents.Add(new Student() { Grade = 25, Name = "Pedro" });

      listStudents.GroupBy(g => g.Name).OrderBy(g => g.Key).SelectMany(g => g.OrderByDescending(x => x.Grade)).ToList().ForEach(x => Console.WriteLine(x.ToString()));
    }
}

Upvotes: 6

Ani
Ani

Reputation: 113402

I think you want an additional projection that maps each group to a sorted-version of the group:

.Select(group => group.OrderByDescending(student => student.Grade))

It also appears like you might want another flattening operation after that which will give you a sequence of students instead of a sequence of groups:

.SelectMany(group => group)

You can always collapse both into a single SelectMany call that does the projection and flattening together.

EDIT: As Jon Skeet points out, there are certain inefficiencies in the overall query; the information gained from sorting each group is not being used in the ordering of the groups themselves. By moving the sorting of each group to come before the ordering of the groups themselves, the Max query can be dodged into a simpler First query.

Upvotes: 18

Related Questions