Sql Case or IF or Temp Table or ???

i have table contains recno, ctype, date.  few typical rows:

20, agtrmp40, 2010-04-08 00:00:00.000
20, firarm, 2010-04-08 00:00:00.000

what i'm trying accomplish able extract date agtrmp40 when have providing date agtrmp40 more recent firarm record, otherwise, firarm record newer , i'd use that.  can't seem work.  initial select information above is: 

select trnhst.recno, trnhst.crsid, min(trnhst.date)   trnhst   crsid in ('qsr','firarm','agtrmp40') , status in ('comp','pass') , trnhst.date >= '2010-01-01 00:00:00' group trnhst.recno, trnhst.crsid  order recno, crsid

any guidance appreciated.  can't figure 1 out!

thank you!

we can this

  ;with cte as (select trnhst.recno, trnhst.crsid, trnhst.date,   

  row_number() over (partition by recno order by [date] desc) as row from trnhst  where crsid in ('qsr','firarm','agtrmp40') and status in ('comp','pass') and trnhst.date >= '2010-01-01 00:00:00')  select * from cte where row = 1 order by recno, crsid   

 

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Transact-SQL