I have the following table
Leaving aside the odd table design, you can use rows preceeding to do this. Basically, if the animal in the current row = the animal in the preceding row, set it to 0, otherwise set it to 1.
create multiset volatile table vt (animal varchar(10)) on commit preserve rows; insert into vt values ( 'cat' ); select animal, case when max(animal) over (partition by animal order by animal rows between 1 preceding and 1 preceding) = animal then 0 else 1 end as test from vt order by animal, test desc
Which will give you:
cat 1 cat 0 dog 1 dog 0 dog 0