Sindhu Ravi (7/28/2014)
You can do this as below (note: Check for the performance issues before implementing)declare @date datetime
select @date=MAX(date) from #mytable where method='B'
;with CTE
as
(
select [DATE],method from #mytable where method in ('A','B') )
select [DATE],method from cte where (method='A' and [DATE]<>@date) or (date=@date and method='B')
This doesn't meet the requirements based on the expected results. If you add a record with Method = 'A' with a date greater the the max date for a record with Method = 'B' the 'A' record with the later date will be displayed.
A pet peeve, but the semicolon in front of the WITH belongs at the end of the preceding statement. Semicolons are statement terminators, not statement begininators.
You may want to start getting used to using them properly as eventually they may become mandatory. The MERGE statement already requires that a semicolon be used as a terminator.