Click here to Skip to main content
14,450,027 members
Rate this:
Please Sign up or sign in to vote.
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 6:51am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

Check this out:

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
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Another solution:

Setup:
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:
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()
   
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!

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100