How to get the minimum value of a DataTable's column?

Question

This is what i've tried:

Decimal tempLow = 0;

for (int irow = 0; irow < dtRep.Rows.Count; irow++)
{
    DataRow row = dtRep.Rows[irow];
    if (irow == 0)
        tempLow = Convert.ToDecimal(row[col.ToString()]);

    if (tempLow > Convert.ToDecimal(row[col.ToString()]))  
        tempLow = Convert.ToDecimal(row[col.ToString()]);
}

Can you be more specific on what the problem is? Please try to explain it in the question, not in the title. Where is the code not doing what you expect it to do, what are the conditions of the objects at that time, and what is it doing instead? — David, Feb 21 '12 at 14:49

Tim Schmelter · Accepted Answer · 2012-02-22T19:35:16.047

You can use LINQ to get the lowest value without your cumbersome loop.

var lowest = dtRep.AsEnumerable()
            .Where(r=> r.Field<Decimal>(col) > 0)
            .Min(r  => r.Field<Decimal>(col));

If you're using .NET 2.0 or lower, you could use DataTable.Compute:

lowest = System.Convert.ToDecimal(dtRep.Compute("MIN(ColName)", "ColName > 0"));

Edit: As mentioned in a comment to my answer, you're also iterating all DataColumns of the DataTable, hence the column-names are dynamic.

Try this approach which loops all columns, checks if the column's DataType is "numeric" and detects the lowest value of all rows in all columns:

var numericTypes = 
   new [] { typeof(Byte),   typeof(Decimal), typeof(Double),
            typeof(Int16),  typeof(Int32),   typeof(Int64),  typeof(SByte),
            typeof(Single), typeof(UInt16),  typeof(UInt32), typeof(UInt64)};

Decimal lowest=Decimal.MaxValue;

foreach(DataColumn col in dtRep.Columns) {
    if(numericTypes.Contains(col.DataType)){
        var exp=string.Format("MIN({0})"  , col.ColumnName);
        var filter=string.Format("{0} > 0", col.ColumnName);
        var d = Convert.ToDecimal(dtRep.Compute(exp, filter));
        if(d < lowest) lowest = d;
    }
}

Note: Exclude the typecheck if you know that all columns are numeric or that they contain strings which can be converted to decimals. (DataTable.Compute will not throw an error if the DataColumn's DataType is a String, but Convert.ToDecimal will if the string is not convertible)

Finally: Here's the "bruteforce" way, iterating all values in the DataTable and trying to parse them to a Decimal:

foreach(DataRow row in dtRep.Rows){
    foreach(Object value in row.ItemArray) {
        try {
            Decimal d = Convert.ToDecimal(value);
            if(d > 0 && d < lowest) lowest = d;
        } catch(Exception ex) { }
    }
}

@gopi-nath-pandraju: I've also provided the classic ADO.NET approach([DataTable.Compute](http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx).). It's perfect for your requirement. — Tim Schmelter, Feb 22 '12 at 08:28
@Gopi: I don't know the name of the column you want the lowest value from, hence i've called it `ColName`. Adjust it accordingly. — Tim Schmelter, Feb 22 '12 at 12:05
@gopi nath pandraju: What does that mean? You don't know the name of the table's columns? But in your question you are iterating all rows and trying to find the lowest value in a column by `row[col.ToString()]`. So `col.ToString()` should be the column name, isn't it? — Tim Schmelter, Feb 22 '12 at 16:39
@gopy: Pardon? My code does not even contain one occurence of `ToString`. Please be more proactive, i've provided multiple working solutions. And please elaborate more on your questions in future. Have a look at this reading on how to ask good questions on SO: http://tinyurl.com/so-hints — Tim Schmelter, Feb 22 '12 at 18:54
@gopi nath pandraju: My last attemp, edited my answer to show you a way that comes closest to your original code. Consider to [accept answers](http://meta.stackexchange.com/a/5235/147438) that solve your questions ;) — Tim Schmelter, Feb 22 '12 at 19:37

dice · Answer 2 · 2012-02-21T16:06:38.893

0

so you are trying to calculate the minimum non-zero value?

decimal value = Convert.ToDecimal(row[col.ToString()];
if(tempLow > value && value > 0.0m)
    tempLow = value;

edited Feb 21 '12 at 16:06

answered Feb 21 '12 at 14:55

dice

2,820
1
23
34

it can - possibly you got an error because I left off the m making it a double - casting to (decimal)0 or using 0.0m fixes it. decimals can be compared for most human purposes - good post here: http://stackoverflow.com/questions/618535/what-is-the-difference-between-decimal-float-and-double-in-c – dice Feb 21 '12 at 16:08

How to get the minimum value of a DataTable's column?

2 Answers2