Using Linq to select item with maximum value in a group

Question

I have a table like that :

dt = New DataTable()
dt.Columns.AddRange(New DataColumn() {
        New DataColumn("time"),
        New DataColumn("fname", Type.GetType("System.String")),
        New DataColumn("note", Type.GetType("System.String")),
        New DataColumn("du", Type.GetType("System.Int32")),
        New DataColumn("site", Type.GetType("System.String"))})
dt.Rows.Add(New Object() {"2023-01-28 02:01", "aaa1", "xxx11xxxxxxx", 100, "a"})
dt.Rows.Add(New Object() {"2023-01-28 03:01", "bbb1", "xxxx22xxxxxx", 2, "b"})
dt.Rows.Add(New Object() {"2023-01-28 09:01", "ccc", "xxxx33xxxxxx", 3, "c"})
dt.Rows.Add(New Object() {"2023-01-28 02:01", "aaa2", "xxx44xxxxxxx", 3, "a"})
dt.Rows.Add(New Object() {"2023-01-28 03:01", "bbb2", "xxx55xxxxxxx", 53, "b"})
dt.Rows.Add(New Object() {"2023-01-28 03:01", "bbb3", "xxx66xxxxxxx", 89, "b"})
dt.Rows.Add(New Object() {"2023-01-28 01:01", "xxx", "xxx77xxxxxxx", 5, "x"})

I want to use linq to query the above table,Group by two columns time and site from a datatable, then get the fname that have maximum du I use the following code:

Dim MYquery = (From p In dt.Select()
    Group p By ID = New With _
                    {Key .time = p("time").ToString.Trim, _
                    Key .site = p("site")} _
                    Into Group Select Group(0)).ToArray.CopyToDataTable

The result was as shown in the photo .

the desired table is :

time	fname	note	du	site
2023-01-28 02:01	aaa1	xxx11xxxxxxx	100	a
2023-01-28 03:01	bbb3	xxx55xxxxxxx	53	b
2023-01-28 09:01	ccc	xxxx33xxxxxx	3	c
2023-01-28 01:01	xxx	xxx77xxxxxxx	5	x

What should I do?

Answer 1

(I am a C# guy, I hope converted from C# correctly): BTW Your sample desired output do not match what you described. It should be the row with 89 for site "b", no?

Dim myQuery = (From r In dt.Select() 
    Group r By ID = New With {
        Key .Time = r.Field(Of String)("Time"), 
        Key .Site = r.Field(Of String)("Site")
    } Into Group 
    Let mx = Group.Max(Function(gg) gg.Field(Of Integer)("Du"))
    From row In Group
    Where row.Field(Of Integer)("Du") = mx
    Select row).CopyToDataTable()

time	fname	note	du	site
2023-01-28 02:01	aaa1	xxx11xxxxxxx	100	a
2023-01-28 03:01	bbb3	xxx66xxxxxxx	89	b
2023-01-28 09:01	ccc	xxxx33xxxxxx	3	c
2023-01-28 01:01	xxx	xxx77xxxxxxx	5	x