Click here to Skip to main content
16,016,535 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Source Table

Ct	Ad	Qua	Price
A	1	34	700
A	2	53	600
A	3	654	500
B	3	45	1200
B	53	434	500
B	5	63	200
B	3	35	200
B	5	63	100 
C	31	45	2200
C	53	434	500
C	51	63	200
C	32	235	200
C	52	63	100



Result Table
Ct	Ad	Qua	Price 
A	1	34	700
B	3	45	1200
C	31	45	2200 


What I have tried:

I want to linq query for that result table in vb.net.
"Ct" field must be groupped and i will get in that group "price" max.

My english is not good. Thank you very much.
Posted
Updated 12-Feb-20 5:51am
v3

Another solution:

Setup:
VB.NET
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn(){
    New DataColumn("Ct", GetType(String)),
    New DataColumn("Ad", GetType(Integer)),
    New DataColumn("Qua", GetType(Integer)),
    New DataColumn("Price", GetType(Integer))})
		
dt.Rows.Add(New Object(){"A", 1, 34, 700})
dt.Rows.Add(New Object(){"A", 2, 53, 600})
dt.Rows.Add(New Object(){"A", 3, 654, 500})
dt.Rows.Add(New Object(){"B", 3, 45, 1200})
dt.Rows.Add(New Object(){"B", 53, 434, 500})
dt.Rows.Add(New Object(){"B", 5, 63, 200})
dt.Rows.Add(New Object(){"B", 3, 35, 200})
dt.Rows.Add(New Object(){"B", 5, 63, 100})
dt.Rows.Add(New Object(){"C", 31, 45, 2200})
dt.Rows.Add(New Object(){"C", 53, 434, 500})
dt.Rows.Add(New Object(){"C", 51, 63, 200})
dt.Rows.Add(New Object(){"C", 32, 235, 200})
dt.Rows.Add(New Object(){"C", 52, 63, 100})
Query:
VB.NET
Dim result As DataTable = dt.AsEnumerable().
    GroupBy(Function(r) r.Field(Of String)("Ct"), Function(key, rows) rows.OrderByDescending(Function(r) r.Field(Of Integer)("Price")).First()).
    CopyToDataTable()
 
Share this answer
 
Comments
Maciej Los 12-Feb-20 12:03pm    
5ed!
luthier 12-Feb-20 12:58pm    
I could not believe this query. Till i saw the result. You are a Hero too. Thank you so much!
Check this out:

VB.NET
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn(){
		New DataColumn("Ct", Type.GetType("System.String")),
		New DataColumn("Ad", Type.GetType("System.Int32")),
		New DataColumn("Qua", Type.GetType("System.Int32")),
		New DataColumn("Price", Type.GetType("System.Int32"))})
dt.Rows.Add(New Object(){"A", 1, 34, 700})
dt.Rows.Add(New Object(){"A", 2, 53, 600})
dt.Rows.Add(New Object(){"A", 3, 654, 500})
dt.Rows.Add(New Object(){"B", 3, 45, 1200})
dt.Rows.Add(New Object(){"B", 53, 434, 500})
dt.Rows.Add(New Object(){"B", 5, 63, 200})
dt.Rows.Add(New Object(){"B", 3, 35, 200})
dt.Rows.Add(New Object(){"B", 5, 63, 100})
dt.Rows.Add(New Object(){"C", 31, 45, 2200})
dt.Rows.Add(New Object(){"C", 53, 434, 500})
dt.Rows.Add(New Object(){"C", 51, 63, 200})
dt.Rows.Add(New Object(){"C", 32, 235, 200})
dt.Rows.Add(New Object(){"C", 52, 63, 100})

Dim result = dt.AsEnumerable() _
	.GroupBy(Function(x) x.Field(Of String)("Ct")) _
	.Select(Function(grp) New With _
	{ _
		.Ct = grp.Key, _
		.Ad = grp.OrderByDescending(Function(x) x.Field(Of Integer)("Price")) _
			.Select(Function(x) x.Field(Of Integer)("Ad")).FirstOrDefault(), _
		.Qua = grp.OrderByDescending(Function(x) x.Field(Of Integer)("Price")) _
			.Select(Function(y) y.Field(Of Integer)("Qua")).FirstOrDefault(), _
		.Price = grp.Max(Function(z) z.Field(Of Integer)("Price")) _
	}) _
	.ToList()

Console.WriteLine(String.Format("{0} {1} {2} {3}", "Ct", "Ad", "Qua", "Price"))
For Each item In result
	Console.WriteLine(String.Format("{0} {1} {2} {3}", item.Ct, item.Ad, item.Qua, item.Price))
Next
 
Share this answer
 
v3
Comments
Richard Deeming 12-Feb-20 11:52am    
It would probably be better to sort the grouped rows by price descending and take the first row, rather than running Max three times, using it to filter the grouped rows, and then projecting a single column from it. :)
Maciej Los 12-Feb-20 12:02pm    
You're right, as always, Richard.
[EDIT]
Updated!
I wanted to share the solution different than yours.
luthier 12-Feb-20 12:54pm    
Thank you very much. You are a Hero!
Maciej Los 12-Feb-20 13:20pm    
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900