Get the minimum date if the consecutive code is same

Database
KVB
Enthusiast

Get the minimum date if the consecutive code is same

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..

Thanks

KVB

1 REPLY
KVB
Enthusiast

Re: Get the minimum date if the consecutive code is same

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))

FROM KBB

 

Correct me ,if I am wrong