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?
CodePudding user response:
(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 |