CODEWITHSUNDEEP
c#mysqlsqldatatable
MatthewD
MatthewD

Reputation: 6761

Get the highest value from a given column in a datatable

I have a datatable that has a column "Pending Approval Number". I need to get the highest number in that column to display to a user as the next available. I have seen some examples of how to do this but I have not been able to get them to work. I know i could loop each DataRow in the DataTable and check the value and store it if it is higher than the last. But I know there has to be a better way.

Here is how the DataTable is filled.

strSQL = "Select * from JobInvoice";
DataTable dtApprovalNumber = new DataTable();
MySqlDataAdapter daApprovalNumber = new MySqlDataAdapter(strSQL, conn);
daApprovalNumber.Fill(dtApprovalNumber);

A change to the SQL query or code to pull it from the datatable are both welcome.

EDIT: After getting the solution for my original numeric column, I found the second column that I need to do this for is string. The solution was also provided below.

Upvotes: 0

Views: 4447

Answers (2)

MajkeloDev
MajkeloDev

Reputation: 1661

If You want to get highest Value from DataTable in code not in sql, then You can just use linq like below:

int highestNumber = dtApprovalNumber.AsEnumerable().Max(x => x.Field<int>("SomeIntegerColumn");

EDIT.

According to Your comment - if You want to calculate max value from a string column which holds numbers(don't get it why) You can go with something like that:

int highestNumber = dtApprovalNumber.AsEnumerable().Max(x => int.Parse(x.Field<string>("SomeStringColumn")));

Please Note that if any of those string values is not convertable it will fail then You will have to do it other way.

EDIT.2

Since I've just tried it I'll share with You - the situation when You have string Column and You are not sure if all of them are convertable(for example some might be empty). See below:

int tempVariable;
int highestNumber = dt.AsEnumerable()
            .Where(x => int.TryParse(x.Field<string>("SomeColumn"), out tempVariable))
            .Max(m => int.Parse(m.Field<string>("SomeColumn")));

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Select max(`Pending Approval Number`) from JobInvoice

You can do a maxvalue + 1 to show it as next available number.

Upvotes: 2

Related Questions