Highest Value in each Group using LINQ

In this post, I will show you how to fetch the maximum value in each Group using LINQ

Let us consider you have the following class called Employees

C#

class Employees
{
public int EmpId { get; set; }
public int DeptId { get; set; }
public int Salary { get; set; }
}

You want to find out the highest salary of an Employee in each Department. Here’s the LINQ query:

C#

List<Employees> emp = new List<Employees>();
// populate emp – demo by devcurry
// emp.Add(new Employees() { EmpId = 1, DeptId = 1, Salary = 20000 });

var highest = from e in emp
group e by e.DeptId into dptgrp
let topsal = dptgrp.Max(x => x.salary)
select new
{
Dept = dptgrp.Key,
TopSal = dptgrp.First(y => y.Salary == topsal).EmpId,
MaximumSalary = topsal
};

Here's a quick description. We first get the maximum salary in a department using

let topsal = dptgrp.Max(x => x.salary)

we then use the ‘topsal’ variable to compare it against the salary of an Employee in that department and then select the Empid

TopSal = dptgrp.First(y => y.Salary == topsal).EmpId

That's it and you will get the Employee with the highest salary in each department. To print the results, just use a loop and print the values - foreach(var a in highest){ }.



Will you give this article a +1 ? Thanks in advance


About The Author

Suprotim Agarwal
Suprotim Agarwal, ASP.NET Architecture MVP (Microsoft Most Valuable Professional) works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He is the Editor of a Developer Magazine called DNC Magazine. He has also written two EBooks 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook

Follow him on twitter @suprotimagarwal

2 comments:

Anonymous said...

If 2 employees have the same highest salary how would you show both employees?

How can we change the query to return all employee id (maybe comma separated) rather than just first employee id?

Anonymous said...

Simply you need to update this query like this.

var highest = from e in emp
group e by e.DeptId into dptgrp
let topsal = dptgrp.Max(x => x.Salary)
select new
{
Dept = dptgrp.Key,
TopSal = string.Join(",", dptgrp.Where(y => y.Salary == topsal).Select(z => z.EmpId).ToList()),
MaximumSalary = topsal
};