@walterhevedeich:
Create table #temp1 (St_Name Char(20), St_Code Char(2), St_Date Datetime)
Insert into #temp1 Values ('Arvind','35','2/3/2012')
Insert into #temp1 Values ('Arvind','78','2/24/2012')
Insert into #temp1 Values ('Arvind','78','2/10/2012')
Insert into #temp1 Values ('Ram','35','2/20/2012')
Insert into #temp1 Values ('Krishna','35','4/1/2012')
Insert into #temp1 Values ('Ravi','35','2/1/2012')
Insert into #temp1 Values ('Ravi','78','2/20/2012')
Insert into #temp1 Values ('Ravi','78','2/14/2012')
if exists (select * from #temp1 where St_Code = '78')
select Top 1 * from #temp1
where St_Code = '78'
order by St_Name
Else
select Top 1 * from #temp1
where St_Code = '35' and St_Date+14 < GETDATE()
order by St_Name
Drop table #temp1
go
But, you said:
First I need to check whether the Student Has the st_code=78, if it is not there then need to get the top most student code that contains st_code=35.
What criteria is 'the top most'? Is it based on St_Name? Or is it based on St_Date? Anyway, if the query I gave you fulfill your needs, you could simply change the order by clause.
hth,
foxyland