Getting last records of every distinct value of a column

Question

I have a message table like this:

Id     Message     Column1
--------------------------
1      asdasdd     1
2      dsfsfsf     1
3      safsafs     2
4      afewfff     3
5      adw qwd     2

I want to get last records of every distinct value on Column1, which means rows 2,4 and 5 via this code:

Messages.OrderByDescending(a => a.Id).GroupBy(a => a.Column1).Select(a => a.Key).ToList();

I only needs ids of rows, but nothing returns. I've done it with sql, but couldn't convert to EF lambda.

select max(Id) as Id from [Messages]
group by Column1
order by Id DESC

https://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query so none of these worked? — Seabizkit, Jun 30 '20 at 13:03

MBB · Accepted Answer · 2020-06-30T13:05:39.377

1

You are selecting key which in group by means column1 on which you are grouping the values. One way to get the values as you expected is to try something like below:

var query = Messages.GroupBy(item => item.Column1).Select(group =>   group.Last().Id)
        .OrderBy(item => item.Id)
        .Select(a => a.Id)
        .ToList();

Or to get complete rows:

var query = Messages.GroupBy(item => item.Column1)
        .Select(group => group.Last())
        .OrderBy(item => item.Id)
        .Select(a => a)
        .ToList();

edited Jun 30 '20 at 13:05

answered Jun 30 '20 at 12:57

MBB

1,635
3
9
19

i would say these would be slow in comparison to https://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query – Seabizkit Jun 30 '20 at 13:04

Getting last records of every distinct value of a column

1 Answers1