Rank Distint Items In Order Of Appearance

Hello,

I'm hoping someone can help me out with this. In order to add an sequence dimension in an ssas cube, I need to rank chronological item in a t-sql data set in order of their distinct appearance. I've tried to do this in mdx and there's just no easy way. I've also tried various ways in t-sql to achive, includeing ranking functions, custom udf, etc. To no avail.

The original dataset would look like this.

date, member_id, action
1/1/2008, 123, register
1/2/2008, 123, create_item_a
1/3/2008, 123, create_item_b
1/4/2008, 123, create_item_a
1/5/2008, 123, create_item_c
1/6/2008, 123, create_item_b

I need another column that flags the action in order of appearance, like so

date, member_id, action, appearance_rank
1/1/2008, 123, register, 1
1/2/2008, 123, create_item_a, 2
1/3/2008, 123, create_item_b, 3
1/4/2008, 123, create_item_a,2
1/5/2008, 123, create_item_c,4
1/6/2008, 123, create_item_b,3

So the "register" action would be the first thing a user did, "create_item_a" the second, create_item_b the third. However, the next occurrance of create_item_a would still retain the rank of 2 as it's not a new action for this user. The ranks would be unique to the actions, based on the first time they occurred.

This has been perplexing, but I feel like there is simple way to do this using some of the ranking functions, but just haven't been able to get it figure out.

Any help would be appreciated.

Regards...