CODEWITHSUNDEEP
c#
Krish KvR
Krish KvR

Reputation: 1054

Select MAX-date from Data table dt on condition

*VC_DEVID*  *DT_LODDATE*               *C_INPUTMODE*    *DT_ALTERDATE*
DEVC109         29/Apr/2013 12:50:29        R           30/Apr/2013 12:50:29
DEVC110         29/Apr/2013 12:50:29        M           29/Apr/2013 12:50:29
DEVC111         29/Apr/2013 12:50:29        M           28/Apr/2013 12:50:29
DEVC112         29/Apr/2013 12:50:29        M           27/Apr/2013 12:50:29
DEVC109         29/Apr/2013 12:50:29        R           26/Apr/2013 12:50:29

this is what in my Datatable returs.. I need to select the maximum alterdate where c_INPUTMODE='C' on a string variable

pls help

Upvotes: 0

Views: 7568

Answers (2)

Rich Dolinsky
Rich Dolinsky

Reputation: 111

SELECT dt.* 
FROM 
dt, 
( SELECT max(dt_alterdate) 
  FROM dt 
  where c_inputmode='C') 
AS maxdate 

WHERE maxdate.vc_devid=dt.vc_devid

Upvotes: 0

Habib
Habib

Reputation: 223322

You can use LINQ to DataSet/DataTable for this purpose like:

DateTime maxDate = dt.AsEnumerable()
                      .Where(r => r.Field<string>("C_INPUTMODE") == "C")
                      .Max(r => r.Field<DateTime>("DT_ALTERDATE"));

Or

DateTime maxDate = dt.AsEnumerable()
            .Where(r => r.Field<string>("C_INPUTMODE") == "C")
            .Select(r => r.Field<DateTime>("DT_ALTERDATE"))
            .Max();

If your Column C_INPUTMODE is of char type then your where clause should be:

.Where(r=> r.Field<char>("C_INPUTMODE") == 'C')

Upvotes: 3

Related Questions