CODEWITHSUNDEEP
c#
user2520528
user2520528

Reputation:

Datatable.Select with max(col) and WHERE clause?

I have a datatable with the following data.

Week    Score           Rank
1       75.8300000000   1
2       76.6800000000   9
3       79.8787870000   4
4       0               0
5       0               0
6       0               0
7       0               0
...
53      0               0

If the datatable only had the first 3 weeks (weeks with Rank), then the following line would work perfectly. But now I need to add a WHERE clause that gets MAX(Week) where Score is greater than 0. In this case, the row returned would be Week 3. Essentially, the query would be SELECT max(week) From datatable where Rank > 0

DataRow[] dr = dsRanks.Tables[0].Select("Week = MAX(Week)");

I tried the following, but I get an index error because 0 rows are returned.

DataRow[] dr = dsRanks.Tables[0].Select("Week = MAX(Week) and Rank > 0");

Any help is appreciated.

Thanks.

Upvotes: 1

Views: 2028

Answers (2)

tinstaafl
tinstaafl

Reputation: 6948

It looks to me that you want the data row with the highest numbered week that has valid data. Something like this should do it:

var dr = dsRanks.Tables[0].Select("Rank > 0", "Week ASC"). LastOrDefault();

As per your example dr is the data row for Week 3.

Upvotes: 0

Servy
Servy

Reputation: 203820

It's easier to solve using LINQ instead:

var week = dsRansk.Tables[0].AsEnumerable()
    .Where(row => row.Field<int>("Rank") > 0)
    .Max(row => row.Field<int>("Week"));

Upvotes: 6

Related Questions