Fetch an attribute from database corresponding to maximum attribute

Question

I have to get bill_version from my table reports where the bill number is maximum
(There can be multiple bill numbers with same value but categorized by their bill_version)

I'm a newbie to C# and do not know the appropriate syntax regarding the same. So far I'm able to get the maximum bill number in the variable maxBillNumber. I do not want to run another query for getting that bill version. How can modify this code according to my need?

Here's the code:

int maxBillNumber =0;
if (dt1.Rows.Count > 0)
{
     foreach (DataRow dr in dt1.Rows)
     {
         int bill_number1 = dr.Field<int>("bill_number");
         maxBillNumber = Math.Max(maxBillNumber, bill_number1);
     }
}

Thanks in advance!

EDIT
I have to get maximum value of another column bill_version corresponding to that maximum row containing bill_number without running another query.

bill_number  bill_version  
1            0  
2            0  
3            0  
4            1  
4            2  
4            3

I have fetched 4 already. Now I have to get that corresponding 3

`int maxBillNumber = Convert.ToInt32(dt1.Compute("max(bill_number)", string.Empty));` — Jeremy Thompson, Jan 18 '17 at 05:21
Try and do research before you ask questions. I am going to close this as a duplicate, let me know if it doesn't answer your question. — Jeremy Thompson, Jan 18 '17 at 05:22
I guess you **misunderstood** my question. I have fetched the maximum value, you can see the above code for that. Now **I have to get value of another column corresponding to that maximum row without running another query.** @JeremyThompson — Shubham Katta, Jan 18 '17 at 05:32
Ok just google **C# DataTable Select** `DataRow[] result = dt1.Select("bill_number = " + maxBillNumber); foreach (DataRow row in result) { Console.WriteLine("{0}", row[0]); }` Because the data is all in memory as a DataTable this code isn't another query. — Jeremy Thompson, Jan 18 '17 at 05:38
Sorry I did misunderstand but this is a duplicate of over a hundred questions other so my advice to **research before asking still stands**. http://stackoverflow.com/questions/7836037/datatable-select-method — Jeremy Thompson, Jan 18 '17 at 05:44
Okay, thanks for the link though :) I'll research more next time. — Shubham Katta, Jan 18 '17 at 06:02

score 2 · Answer 1 · answered Jan 18 '17 at 06:02

        var table = new DataTable();
        table.Columns.Add("BillNumber", typeof(int));
        table.Columns.Add("BillVersion", typeof(int));

        // Here we add five DataRows.
        table.Rows.Add(1, 0);
        table.Rows.Add(2, 0);
        table.Rows.Add(3, 0);
        table.Rows.Add(4, 1);
        table.Rows.Add(4, 2);
        table.Rows.Add(4, 3);
        var dataRows = table.AsEnumerable().ToList();

        var result = dataRows.OrderByDescending(x => x.ItemArray[0]).ThenByDescending(x => x.ItemArray[1]).FirstOrDefault();

        if (result != null)
        {
            Console.WriteLine(result.ItemArray[0].ToString());
            Console.WriteLine(result.ItemArray[1].ToString());
        }

Jeremy Thompson · Answer 2 · 2017-01-18T06:27:23.357

0

int maxBillNumber = Convert.ToInt32(dt1.Compute("max([bill_number])", string.Empty));

DataRow[] result = dt1.Select("bill_number = " + maxBillNumber); 
foreach (DataRow row in result) 
{
   Console.WriteLine("{0}", row[0]); 
}

edited Jan 18 '17 at 06:27

answered Jan 18 '17 at 06:04

Jeremy Thompson

61,933
36
195
321

Fetch an attribute from database corresponding to maximum attribute

2 Answers2