I want to select maximum date data based on some condition i.e.if alloc_dt column has value for c_id, then I want to select maximum date row for that particular c_idand if all rows have NULL for c_id, then I want to select maximum DAYS row for that particular c_id.

/*insert into bfm values(1,1,1,25,1,'1/10/2013')insert into bfm values(2,2,1,25,1,'1/27/2013')insert into bfm values(3,3,1,50,1,'3/01/2013')

--insert into bfm values(4,4,8,50,3,'3/01/2013')

insert into bfm values(5,5,5,400,2,'01/20/2013')

*/

--select * from f--select * from bfm--select * from bf

declare @runDate dateset @runDate = '6/11/2013';

select c.c_id,c.p_id,f.e_dt,@runDate as RunDate,f.f_id,f.amt,bf.actual_dt,bf.bf_id,bf.c_id as bfC_id,bfm.bfm_id,bfm.f_id as bfmF_id,bfm.amt,bfm.alloc_dt,case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) elsedatediff(day,f.e_dt,@runDate) end as Days

so for c_id =1 row with alloc_dt =2013-03-01 should be selected becoz that is maximum date for that c_idfor c_id = 2, row with alloc_dt = 2013-01-20 should be selected becoz that is maximumfor c_id = 3, row with maximum days i..e 41 should be selected because all alloc_dt rows are NULL, so I want maximum days row in this case.

The data you provide has a non-null value in alloc_dt column for c_id = 3,so I was unable to test the alloc_dt = null condition;Here is your code slightly modified (in red) to give you what you want:

declare @runDate date
set @runDate = '6/11/2013';
SELECT * from (select ROW_NUMBER() OVER(PARTITION BY c.c_id Order by alloc_dt DESC,
(case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end) DESC) as RN, c.c_id,c.p_id,
f.e_dt,@runDate as RunDate,f.f_id,f.amt as famt,
bf.actual_dt,bf.bf_id,bf.c_id as bfC_id,
bfm.bfm_id,bfm.f_id as bfmF_id,bfm.amt as bfmamt,bfm.alloc_dt,
case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end as Days
from @c c
inner join @f f on c.c_id = f.c_id
left join @bfm bfm on bfm.f_id = f.F_id
left join @bf bf on bf.bf_id = bfm.bf_id
where f.e_Dt <= @runDate) A
WHERE RN = 1 order by A.c_id, days desc