LINQ query to select records from table where 2 columns have max values

Question

I have the following data in sql server table:

EmployeeId ForMonth ForYear Value 1 1 2015 45000.00 1 6 2015 35000.00 1 12 2014 66666.00 2 1 2015 70000.00 2 5 2015 80000.00

I need to find the LINQ-to-SQL query to select for each EmployeeId the record having max ForMonth and max ForYear. So the result should be:

EmployeeId ForMonth ForYear Value 1 6 2015 35000.00 2 5 2015 80000.00

It's not that I didn't try to solve this before coming and posting this question, but the problem is that I didn't know how to write this query in SQL server, so I don't have any LINQ code to add. Please if you find this a bad question, ask me to delete it.

I have a dream that down voters start to have the kindness to say at least why did they down voted a question and caused points loss for a user :/ — Saleem
– Saleem, Commented Jul 6, 2015 at 15:34
Of course I tried, but I have no decent code to post, I have a problem in figuring out how to write the query in sql server, so I don't have something to write in LINQ. — Saleem
– Saleem, Commented Jul 6, 2015 at 15:36
Ok I'll try and update my question with what I get, Or would you recommend to delete it for now? — Saleem
– Saleem, Commented Jul 6, 2015 at 15:40

Aducci · Accepted Answer · 2015-07-06 16:11:33Z

You only want 1 record per employee, so you need to group by the ID. Then you order your groups by the lastest year/month and select the first one

var query = from e in db.Employees group e by e.EmployeeId into g select g.OrderByDescending(e => e.ForYear) .ThenByDescending(e => e.ForMonth) .FirstOrDefault();

laskdjf · Accepted Answer · 2015-07-06 15:47:35Z

I am assuming you want the record with the greatest ForYear and greatest ForMonth

var result = (from db in MYDBContext orderby db.ForYear descending, db.ForMonth descending select db).First();

Collectives™ on Stack Overflow

LINQ query to select records from table where 2 columns have max values

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related