CT KBB(DT DATE,COD VARCHAR(1))
INS INTO KBB VALUES('2014-01-01','A');
INS INTO KBB VALUES('2014-01-02','A');
INS INTO KBB VALUES('2014-01-03','D');
INS INTO KBB VALUES('2014-01-04','A');
INS INTO KBB VALUES('2014-01-05','D');
INS INTO KBB VALUES('2014-01-06','A');
Here I have to get the 1,2,3,4,5,6 rows..
And I have used the below one and got it.
SEL EFF_DT,COD,RANK() OVER(ORDER BY EFF_DT RESET WHEN COD<>MAX(COD) OVER(ORDER BY EFF_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING))
Correct me ,if I am wrong