Mehmet Ince
Mehmet Ince

Reputation: 4189

How to group DataTable and sort each group by a field?

I have a DataTable that looks like below;

| ItemName | ItemNo |   ItemValue
     A         2            3,1
     B         1            2,2
     C         3            1,5
     A         2            2,0
     B         1            1,4
     A         2            2,7
     C         3            1,3
     C         3            2,1
     B         1            1,9

I want to group above DataTable by ItemName and sort these groups by ItemNo first, then sort the each sorted group inside by ItemValue.

How can I achieve this?

Note: after sorting, I want my table like below;

| ItemName | ItemNo |   ItemValue
     B         1            1,4
     B         1            1,9
     B         1            2,2
     A         2            2,0
     A         2            2,7
     A         2            3,1
     C         3            1,3
     C         3            1,5
     C         3            2,1

Upvotes: 5

Views: 14563

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460228

table = table.AsEnumerable()
             .GroupBy(r => r.Field<string>("ItemName"))
             .OrderBy(g => g.Max(r => r.Field<int>("ItemNo")))
             .SelectMany(g => g.OrderBy(r => r.Field<double>("ItemValue")))
             .CopyToDataTable();

I want to group above DataTable by ItemName and sort these groups by ItemNo first, then sort the each sorted group inside by ItemValue.

But ItemNo is not part of the goup-key, so how do you want to sort the groups by ItemNo if the values are different in each group(as opposed to your sample data)? By max/min/avg value? I have used Max to demonstrate one approach.

Edit So actually both columns belong to the same key, then i would use an anonymous type as grouping key that contains both columns, then it's even clearer:

table = table.AsEnumerable()
             .GroupBy(r => new
             { 
                 ItemName = r.Field<string>("ItemName"),
                 ItemNo   = r.Field<int>("ItemNo") 
             })
             .OrderBy(g => g.Key.ItemNo)
             .SelectMany(g => g.OrderBy(r => r.Field<double>("ItemValue")))
             .CopyToDataTable();

Upvotes: 10

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98830

You can define a SortYourDataTable method like;

public static DataTable SortYourDataTable(DataTable dt, string column)
{
    dt.DefaultView.Sort = column;
    dt = dt.DefaultView.ToTable();
    return dt;
}

Then call it;

SortYourDataTable(SortYourDataTable(SortYourDataTable(dt, "ItemNo"), "ItemName"), "ItemValue");

Upvotes: 3

Related Questions