DotNetCurry Logo

Select the Highest value in each Group or Category using LINQ

Posted by: Suprotim Agarwal , on 11/12/2009, in Category LINQ
Views: 28733
Abstract: In this article, we will see how to select the highest value in a Group or Category using LINQ
Select the Highest value in each Group or Category using LINQ
Sometime back I had written an article on Selecting the Highest value in a Group/Category using SQL Server 2005/2008. A user mailed back asking how to use LINQ to find the highest value in a group/category. This article shows you how to do so.
We will be using the Enumerable.Max method in this example that returns the maximum value in a sequence of values.
To start with, we will use a Student class for our example with the following definition:
class Students
    public int StudentId {get; set;}
    public int SubjectId {get; set;}
    public float Marks { get; set; }
Class Students
    Private privateStudentId As Integer
    Public Property StudentId() As Integer
            Return privateStudentId
        End Get
        Set(ByVal value As Integer)
            privateStudentId = value
        End Set
    End Property
    Private privateSubjectId As Integer
    Public Property SubjectId() As Integer
            Return privateSubjectId
        End Get
        Set(ByVal value As Integer)
            privateSubjectId = value
        End Set
    End Property
    Private privateMarks As Single
    Public Property Marks() As Single
            Return privateMarks
        End Get
        Set(ByVal value As Single)
            privateMarks = value
        End Set
    End Property
End Class
Our task is to list the Student with the highest mark in a Subject. To do so, we will write the following code:
class Program
static void Main(string[] args)
 List<Students> students = new List<Students>();
 students.Add(new Students() { StudentId = 1, SubjectId = 1, Marks = 8.0f });
 students.Add(new Students() { StudentId = 2, SubjectId = 1, Marks = 5.0f });
 students.Add(new Students() { StudentId = 3, SubjectId = 1, Marks = 7.0f });
 students.Add(new Students() { StudentId = 4, SubjectId = 1, Marks = 9.5f });
 students.Add(new Students() { StudentId = 1, SubjectId = 2, Marks = 9.0f });
 students.Add(new Students() { StudentId = 2, SubjectId = 2, Marks = 7.0f });
 students.Add(new Students() { StudentId = 3, SubjectId = 2, Marks = 4.0f });
 students.Add(new Students() { StudentId = 4, SubjectId = 2, Marks = 7.5f });
 var stud = from s in students
            group s by s.SubjectId into stugrp
              let topp = stugrp.Max(x => x.Marks)
            select new
                Subject = stugrp.Key,
                TopStudent = stugrp.First(y => y.Marks == topp).StudentId,
                MaximumMarks = topp
Sub Main()
        Dim students As New List(Of Students)()
        students.Add(New Students() With {.StudentId = 1, .SubjectId = 1, .Marks = 8.0F})
        students.Add(New Students() With {.StudentId = 2, .SubjectId = 1, .Marks = 5.0F})
        students.Add(New Students() With {.StudentId = 3, .SubjectId = 1, .Marks = 7.0F})
        students.Add(New Students() With {.StudentId = 4, .SubjectId = 1, .Marks = 9.5F})
        students.Add(New Students() With {.StudentId = 1, .SubjectId = 2, .Marks = 9.0F})
        students.Add(New Students() With {.StudentId = 2, .SubjectId = 2, .Marks = 7.0F})
        students.Add(New Students() With {.StudentId = 3, .SubjectId = 2, .Marks = 4.0F})
        students.Add(New Students() With {.StudentId = 4, .SubjectId = 2, .Marks = 7.5F})
        Dim stud = _
         From s In students _
         Group s By Key = s.SubjectId Into Group _
     Let topp = Group.Max(Function(x) x.Marks) _
     Select New With {Key .Subject = Key, _
       Key .TopStudent = Group.First(Function(y) y.Marks = topp).StudentId, _
       Key .MaximumMarks = topp}
End Sub
The implementation shown here is quite simple (thanks to Drew M). We first select the maximum marks in a subject using
let topp = stugrp.Max(x => x.Marks)
and then use the same ‘topp’ variable to compare it against the score of a student in that subject and then select the studentid
TopStudent = stugrp.First(y => y.Marks == topp).StudentId
That’s it. The final step is to loop through the results and print the values
 foreach (var student in stud)
    Console.WriteLine("In SubjectID {0}, Student with StudentId {1} got {2}",
For Each student In stud
Console.WriteLine("In SubjectID {0}, Student with StudentId {1} got {2}", _  student.Subject, student.TopStudent, student.MaximumMarks)
Next student
The output is as shown below:
Maximum in Group
I hope you liked this article and I thank you for viewing it. The entire source code of this article can be downloaded over here
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

Page copy protected against web site content infringement 	by Copyscape

Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Geoff on Thursday, November 12, 2009 7:56 AM
The link to go to page 2 is broken in google chrome as is the Insert button for this comment...
Comment posted by Admin on Thursday, November 12, 2009 9:13 PM
@Geoff - We just tried replication the behavior in Chrome just fine..can you tell which version of Chrome are you using.
Comment posted by Gaurav Dixit on Friday, November 13, 2009 12:38 AM
I like to add this article on my blog
Comment posted by Tadas on Saturday, November 14, 2009 7:15 PM
It won't work if there is few students with same Marks in the same group