Finding MAX and MIN based on Grouping in a DataTable

Question

//Edited I have a DataTable I want to Finding MAX and MIN based on Grouping in a DataTable. Here in group column I want to Retain Max and Min value based on Group and delete rest in the same group and when group is null just skip that row Par Name Group Min Max ADC1 C_A - 0.2 1 ADC2 C_A - 2 2 USB1 C_R -3 3 USB2 C_R -4 2 Value1 C_A -5 3 ADC3 C_A -2 5 Value2 -4 1 Output : Par Name Group Min Max Value1 C_A -5 3 ADC3 C_A -2 5 USB2 C_R -4 2 USB1 C_R -3 3 Value -4 1

Vulpes · Answer

OK I've had to clone the datatable to get the rows in the correct order but the following should work: DataTable dt2 = dt.Clone(); var groups = from DataRow dr in dt.Rows group dr by dr[Group] into g select g; foreach(var group in groups) { if(Convert.IsDBNull(group.Key)) { foreach(DataRow row in group) { dt2.Rows.Add(row.ItemArray); } } else { var rowsMin = group.Where (r => !Convert.IsDBNull(r[Min])).OrderBy(r => Convert.ToDouble(r[Min])); var rowsMax = group.Where (r => !Convert.IsDBNull(r[Min])).OrderBy(r => Convert.ToDouble(r[Max])); DataRow minRow = rowsMin.First(); DataRow maxRow = rowsMax.Last(); dt2.Rows.Add(minRow.ItemArray); if(!Object.ReferenceEquals(minRow maxRow)) { dt2.Rows.Add(maxRow.ItemArray); } } } // check it worked foreach(DataRow row in dt2.Rows) { Console.WriteLine(String.Join(  row.ItemArray)); }

Vulpes · Answer

Try this: var groups = from DataRow dr in dt.Rows group dr by dr[Group] into g select g; DataTable[] subsets = new DataTable[groups.Count()]; int i = 0; foreach(var group in groups) { subsets[i] = dt.Clone(); string name = group.Key.ToString(); if (name == ) name = (Empty); subsets[i].TableName = Subset:  + name; foreach(DataRow row in group) { subsets[i].Rows.Add(row.ItemArray); } i++; } // check it worked foreach(DataTable subset in subsets) { Console.WriteLine(subset.TableName); Console.WriteLine(); foreach(DataRow row in subset.Rows) { Console.WriteLine(String.Join( row.ItemArray)); } Console.WriteLine(); }

Farhan Shariff · Answer

You are the Master :) Thank you Vulpes  I am testing it so far It is working fine

Farhan Shariff · Answer

Sorry my bad typing it .Simple re there are many different groups. find MAX and MIN and delete others first find minimum in the group and then find the maximum in the group and then delete the rest in the same group. and when group has null just leave it in the table. Value1 belongs to group C_A and has least value ADC3 belongs to group C_A and has highest value so they are retained.It is comparing based on Max and Min considering the Par Name fall into same group.

Vulpes · Answer

Sorry but I'm still not completely clear what's wanted. If for example we consider the group C_A: the smallest of the Min values (ignoring the null value) is - 5 in the row with Par Name = Value1 the largest of the Max values is 4 in the row with Par Name = ADC2 So are you saying that you want to retain these two rows but delete the other two (with Par Names of ADC1 and ADC3)?

Farhan Shariff · Answer

I don't want to create subsets of datatable in the same table Just want to retain Max and Min by considering groups. Just want to search Max and Min group wise. In one particular group find Max and Min delete other rows with Same group name When group name is null just leave it like that

Vulpes · Answer

Do you mean like this? var groups = from DataRow dr in dt.Rows group dr by dr[Group] into g select g; DataTable[] subsets = new DataTable[groups.Count()]; int i = 0; foreach(var group in groups) { string name = group.Key.ToString(); if (name == ) { subsets[i] = dt.Clone(); subsets[i].TableName = Subset: (Empty; foreach(DataRow row in group) { subsets[i].Rows.Add(row.ItemArray); } } else { subsets[i] = new DataTable(name); subsets[i].Columns.Add(Min typeof(double)); // if original column is double subsets[i].Columns.Add(Max typeof(double)); // ditto foreach(DataRow row in group) { subsets[i].Rows.Add(row[Min] row[Max]); } } i++; } // check it worked foreach(DataTable subset in subsets) { Console.WriteLine(subset.TableName); Console.WriteLine(); foreach(DataRow row in subset.Rows) { Console.WriteLine(String.Join( row.ItemArray)); } Console.WriteLine(); }

Farhan Shariff · Answer

What I exactly want is to retain MAX and MIN value in a particular group.(Search Rows with SAME Group name  find Max and Min retain them and delete the rest ) and when group name is null just leave it like that. So output I will have Max and Min for each group and other rows with Group null

Par Name	Group	Min	Max
ADC1	C_A	- 0.2	1
ADC2	C_A	- 2	2
USB1	C_R	-3	3
USB2	C_R	-4	2
Value1	C_A	-5	3
ADC3	C_A	-2	5
Value2		-4	1