How to count the first occurrence of a value in a column?

[rvijayanth]

I have a table like this: 

ID	TID	Color_Change	Date
1	1	No	date 1
1	2	Yes	date 2
1	3	Yes	date 3
1	4	No	date 4
1	5	No	date 5
1	6	Yes	date 6
1	7	Yes	date 7
1	8	Yes	date 8
2	1	No	date 1
2	2	Yes	date 2
2	3	Yes	date 3

I need to add a column "First_Color_Change" like shown below, which will tag the first time the column "Color_Change" has the value 'Yes' for each ID. 

ID	TID	Color_Change	Date	First_Color_Change
1	1	No	date 1	No
1	2	Yes	date 2	Yes
1	3	Yes	date 3	No
1	4	No	date 4	No
1	5	No	date 5	No
1	6	Yes	date 6	No
1	7	Yes	date 7	No
1	8	Yes	date 8	No
2	1	No	date 1	No
2	2	Yes	date 2	Yes
2	3	Yes	date 3	No