Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
shilpash
Posting Yak Master
103 Posts |
Posted - 2013-02-26 : 02:44:30
|
id asofdate desc1 2/25/2012 a1 2/24/2012 a1 2/23/2012 a1 2/22/2012 a1 2/21/2012 b2 2/25/2012 a2 2/24/2012 b2 2/25/2012 boutput--1 2/22/2012(most recent when value changes from b to a)2 2/25/2012(most recent when value change from b to a)note:desc should not be nullThanks in advance |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 02:59:03
|
[code];WITH CTEAS(SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY asofdate,desc) AS Seq,*FROM table)SELECT id,asofdateFROM(SELECT c1.id,c1.asofdate,ROW_NUMBER() OVER (PARTITION BY c1.id ORDER BY c1.asofdate DESC) AS RnFROM CTE c1INNER JOIN CTE c2On c2.id = c1.idAND c2.Seq = c1.Seq+1AND c2.desc <> c1.desc )tWHERE Rn=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|