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

Question

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

AccountLevel  
0  
1  
2  
3 

Answer 1

select MIN(AccountLevel) AS 'min' from tableName

Is that what you're asking? Your questions is pretty googlable, btw.

http://www.w3schools.com/sql/sql_func_min.asp

Answer 2

SELECT MIN(AccountLevel) AS 'min', MAX(AccountLevel) AS 'max' FROM table;

That covers the "max", too.

Answer 3

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

Answer 4

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.

Answer 5

int minAccountLevel = int.MinValue;
int maxAccountLevel = int.MaxValue;
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.

Answer 6

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 :)