how to query greatest ID of each value in a Sharepoint list?

Question

I have a list with two columns (ID,Value).

I want grouped list with value columns then select greatest Id of it.

ID | Value

1 | 101

2 | 305

3 | 102

4 | 101

5 | 305

6 | 101

Result :

ID | Value

3 | 102

5 | 305

6 | 101

Answer 1

To just show the list you can use a view with "Group By" = "Value" and "Total" : "ID" - Maximum

But if you need this for any data processing purpose or customized showing then LINQ is best option I guess, you can use CAML Query group by but that will be no use for your purpose, so i think something like this is your best option :

DataTable dtList = list.Items.GetDataTable();
var grouped = from row in dtList.AsEnumerable()
              group row by row["Value"] into valueGroup
              select new { Value = valueGroup.Key, ID = valueGroup.Max(id => id["ID"]) };

just wanted to point one very common fact: when you use ID as a column name it will have different internal name, so id["ID"] will be different