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

score 123 · Answer 1 · edited Jan 18 '17 at 06:27

123

Easiar approach on datatable could be:

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

edited Jan 18 '17 at 06:27

Jeremy Thompson

61,933
36
195
321

answered Feb 07 '11 at 11:41

Lalit

4,897
7
32
36

1

This is the best approach but u are missing one thing the use of `[]` in the above code.. e.g `int minLavel = Convert.ToInt32(dt.Compute("min([AccountLevel])", string.Empty));` Without the Square Brackets it gave me an error. – Syed Ali Naqi Sep 28 '16 at 10:12
I am not very experienced with this, but my program got much faster after I started using "compute" instead of loops etc. You just need to make sure your data is in the correct format to be able to use compute. – Birk Jan 19 '19 at 21:20

Aaronaught · Accepted Answer · 2011-04-13T14:14:36.353

57

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.

edited Apr 13 '11 at 14:14

answered Mar 14 '10 at 15:58

Aaronaught

120,909
25
266
342

Yes, this is what SLaks suggests, to use for loop as the fastest way. – Ahmed Atia Mar 14 '10 at 16:03
Yes, but I think it should be { int accountLevel = dr.Field("AccountLevel"); min = Math.Min(min, accountLevel); max = Math.Max(max, accountLevel); } isn't it? – Ahmed Atia Mar 14 '10 at 16:13
@Ahmed: Sorry, typo in the variable assignments. Fixed. – Aaronaught Mar 14 '10 at 19:48
2

I may be a little late to the party, but won't minAccountLevel ALWAYS be equal to int.MinValue because accountLevel can never be less than int.MinValue? And the same goes for maxAccountLevel and int.MaxValue, no? – Welton v3.62 Apr 13 '11 at 14:13
@Welton yeah, I messed up and transposed the first two lines. The "min" should be initialized to `Int32.MaxValue` and the "max" should be initialized to `Int32.MinValue`, such that the first row (if there are any rows at all) will always update those. – Aaronaught Apr 13 '11 at 14:15
@Aaronaught good to know this is as fast as linq as it was my approach too, though chaowman's answer looks interesting... – Alex Jul 26 '11 at 09:11

Cylon Cat · Answer 3 · 2010-03-14T15:50:38.930

16

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.

edited Mar 14 '10 at 15:50

answered Mar 14 '10 at 15:16

Cylon Cat

7,111
2
25
33

But it requires two iterations. – SLaks Mar 14 '10 at 15:21
Somehow I don't think that `Distinct` will speed this up - it has to hash every element, whereas `Min` and `Max` just do a compare. – Aaronaught Mar 14 '10 at 15:55

score 12 · Answer 4 · answered Sep 27 '13 at 06:37

12

This worked fine for me

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

answered Sep 27 '13 at 06:37

Animesh Anand

314
4
13

This is cooooool! Awesome – Venugopal M Jun 16 '22 at 09:02

score 6 · Answer 5 · answered Mar 14 '10 at 15:05

6

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

answered Mar 14 '10 at 15:05

SLaks

868,454
176
1,908
1,964

score 5 · Answer 6 · answered Mar 14 '10 at 15:59

5

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

answered Mar 14 '10 at 15:59

Chaowlert Chaisrichalermpol

5,272
3
26
29

haven't tried this yet, but it looks promising - have you compared it to the straight-up for loop for performance? – Alex Jul 26 '11 at 09:09
1

what type is `accountTable`? DataTable objects don't seem to support `Aggregate` for me... – hdgarrood Apr 04 '13 at 11:10

score 4 · Answer 7 · edited Feb 28 '12 at 21:56

4

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

edited Feb 28 '12 at 21:56

animuson

53,861
28
137
147

answered Feb 23 '12 at 13:50

user1228498

41
1

I tested this on a DataTable containing 125k rows. It is terribly slow. – Merin Nakarmi May 15 '19 at 21:19

score 2 · Answer 8 · answered Jul 15 '15 at 07:28

2

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

answered Jul 15 '15 at 07:28

Musakkhir Sayyed

7,012
13
42
65

Please add a comment on your answer, code only could not be clear. – michelem Jul 15 '15 at 07:52

score 2 · Answer 9 · edited Feb 16 '17 at 21:26

2

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

edited Feb 16 '17 at 21:26

Hallgrim

15,143
10
46
54

answered Feb 16 '17 at 21:15

Bibek Adhikari

21
1

4

Perhaps you can elaborate on **why** this is is the fastest way? Any arguments can be helpful for others to take or skip your advice in favour of the other posts. – Juliën Feb 16 '17 at 21:35

score 1 · Answer 10 · answered Jul 17 '15 at 14:30

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();

score 0 · Answer 11 · edited Oct 28 '12 at 07:54

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.

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

11 Answers11

Linked

Related