How to return rows with max value one column grouped by another column?

Question

this is data

id       code       status
1          1          5
2          1          6
3          2          8
4          2          9
5          2          12
6          3          15
7          3          19
8          3          13

what I need as a result is this:

id       code       status
2          1          6
5          2          12
8          3          19

in other words I need al rows with max id in grouping by code.

for example, for code 3, ids are 6, 7, 8 and I need row with 8.

equivalent sql query would be

select * 
from t inner join (select max(id) maxId from t group by code) t1
on t.id = t1.maxId

Status column is irelevant, it's just an atribute.

What is the linq query for something like this?

Thanks in advance.

Answer 1

from t in mytable
where !(from tt in mytable
        where tt.code == t.code &&
              tt.id > t.id
        select tt
       ).Any()
select t

Answer 2

Literal pick-a-winner

from row in rows
group row by row.code into g
let winner =
(
  from groupedrow in g
  order groupedrow  by groupedrow.id desc
  select groupedrow
).First()
select winner;

More traditional pick-a-winner (no chance of automatic group-element-fetching roundtrips)

var subquery =
  from row in rows
  group row by row.code into g
  select new {Code = g.Key, Id = g.Max(row => row.Id)};

var query =
  from row in rows
  join key in subquery on new{row.Code, row.Id} == key
  select row;

Exact match to your posted sql:

IQueryable<int> subquery =
  from row in rows
  group row by row.code into g
  select g.Max(row => row.Id);

var query =
  from row in rows
  join key in subquery on row.Id == key
  select row;