Geek asks and answers

Find the row with the maximum value for each key

I have a table stored in SQL Server that I hope to query LINQ for. Vague description of the table:

  ID1  |  ID2  |  SomeData
-----------------------------
   1   |   1   |  'Moo'
   1   |   2   |  'Meow'
   1   |   3   |  'Woof'
   1   |   4   |  'Cheap'
   2   |   1   |  'Quack'
   2   |   2   |  'Grrrrr'
   2   |   3   |  'Wan wan'

I'm only interested in the lines where ID2is the highest value for an ID1example of the desired results:

   1   |   4   |  'Cheap'
   2   |   3   |  'Wan wan'

I came up with this SQL code:

SELECT *
FROM SomeTable a
INNER JOIN
(
    SELECT ID1, MAX(ID2) as SomeName
    FROM SomeTable
    GROUP BY ID1
) as b
ON a.ID1 = b.ID1 and a.ID2 = b.SomeName

When implemented in LINQ, is a sub query also required? That is, query the database and load the variable with the results of the "internal query", then query the same table, but compare with the values ​​from the "internal query"? I know that if I work a little with this, I will get it in the end, but I feel that there is probably some fancy way to do this in LINQ very easily.

The desired answer will use the syntax of the extension method.

: - LINQ ? , LINQ? , # (ASP.NET MVC ) LINQ.

+5
1

:

var result = db.SomeTable.GroupBy(x => x.ID).Select(g => g.OrderByDescending(x => x.ID2).First());

, SQL , , , !

+11

More articles:


All Articles