CODEWITHSUNDEEP
c#.netselectdatatable
Ahmed Atia
Ahmed Atia

Reputation: 17960

How to select min and max values of a column in a datatable?

For the following datatable column, what is the fastest way to get the min and max values?

AccountLevel  
0  
1  
2  
3

Upvotes: 73

Views: 251939

Answers (11)

Bibek Adhikari
Bibek Adhikari

Reputation: 21

Session["MinDate"] = dtRecord.Compute("Min(AccountLevel)", string.Empty);
Session["MaxDate"] = dtRecord.Compute("Max(AccountLevel)", string.Empty);

Upvotes: 2

Lalit
Lalit

Reputation: 4957

Easiar approach on datatable could be:

int minLavel = Convert.ToInt32(dt.Compute("min([AccountLevel])", string.Empty));

Upvotes: 126

dbarth
dbarth

Reputation: 238

Performance wise, this should be comparable. Use Select statement and Sort to get a list and then pick the first or last (depending on your sort order).

var col = dt.Select("AccountLevel", "AccountLevel ASC");

var min = col.First();
var max = col.Last();

Upvotes: 1

Musakkhir Sayyed
Musakkhir Sayyed

Reputation: 7170

var min = dt.AsEnumerable().Min(row => row["AccountLevel"]);
var max = dt.AsEnumerable().Max(row => row["AccountLevel"]);

Upvotes: 2

Animesh Anand
Animesh Anand

Reputation: 324

This worked fine for me

int  max = Convert.ToInt32(datatable_name.AsEnumerable()
                        .Max(row => row["column_Name"]));

Upvotes: 12

ZeroOne
ZeroOne

Reputation: 791

I don't know how my solution compares performance wise to previous answers.

I understand that the initial question was: What is the fastest way to get min and max values in a DataTable object, this may be one way of doing it:

DataView view = table.DefaultView;
view.Sort = "AccountLevel";
DataTable sortedTable = view.ToTable();
int min = sortedTable.Rows[0].Field<int>("AccountLevel");
int max = sortedTable.Rows[sortedTable.Rows.Count-1].Field<int>("AccountLevel");

It's an easy way of achieving the same result without looping. But performance will need to be compared with previous answers. Thought I love Cylon Cats answer most.

Upvotes: 0

user1228498
user1228498

Reputation: 41

another way of doing this is 

int minLavel = Convert.ToInt32(dt.Select("AccountLevel=min(AccountLevel)")[0][0]);

I am not sure on the performace part but this does give the correct output

Upvotes: 4

Aaronaught
Aaronaught

Reputation: 122654

int minAccountLevel = int.MaxValue;
int maxAccountLevel = int.MinValue;
foreach (DataRow dr in table.Rows)
{
    int accountLevel = dr.Field<int>("AccountLevel");
    minAccountLevel = Math.Min(minAccountLevel, accountLevel);
    maxAccountLevel = Math.Max(maxAccountLevel, accountLevel);
}

Yes, this really is the fastest way. Using the Linq Min and Max extensions will always be slower because you have to iterate twice. You could potentially use Linq Aggregate, but the syntax isn't going to be much prettier than this already is.

Upvotes: 57

Chaowlert Chaisrichalermpol
Chaowlert Chaisrichalermpol

Reputation: 5402

var answer = accountTable.Aggregate(new { Min = int.MinValue, Max = int.MaxValue }, 
                                        (a, b) => new { Min = Math.Min(a.Min, b.Field<int>("AccountLevel")),
                                                        Max = Math.Max(a.Max, b.Field<int>("AccountLevel")) });
int min = answer.Min;
int max = answer.Max;

1 iteration, linq style :)

Upvotes: 5

Cylon Cat
Cylon Cat

Reputation: 7201

Use LINQ. It works just fine on datatables, as long as you convert the rows collection to an IEnumerable.

List<int> levels = AccountTable.AsEnumerable().Select(al => al.Field<int>("AccountLevel")).Distinct().ToList();
int min = levels.Min();
int max = levels.Max();

Edited to fix syntax; it's tricky when using LINQ on DataTables, and aggregating functions are fun, too.

Yes, it can be done with one query, but you will need to generate a list of results, then use .Min() and .Max() as aggregating functions in separate statements.

Upvotes: 16

SLaks
SLaks

Reputation: 887459

The most efficient way to do this (believe it or not) is to make two variables and write a for loop.

Upvotes: 6

Related Questions