How can i fill up this column!
Posted on 2005-04-29
I have this Table
CONSTRAINT my_Unique_stuff UNIQUE (my_id, my_Index)
The value in the column "my_auto_Index" will be generated automaticly. See exemple below for the rules and explanation
my_id my_Index my_auto_Index
1 aaa 1 <-- its going to be 1 its the first time aaa appear in "my_Index" column
1 bbb 2 <-- its going to be 2 its the first time bbb appear in "my_Index" column, but it's the second different value
2 aaa 1 <-- It's going to be 1 since this value already existed in "my_Index" column and it was the first value
2 ccc 3 <-- first time ccc appear and its 3, because its the third different value to appear in the column my_Index
2 bbb 2 <-- and so on
3 ddd 4 <-- it's 4, ddd is the fourth different value in column "my_Index"
Don't know if this is clear enough.
Now what im wondering , how can i do this, and whats the best way to do this. i was thinking maybe a trigger could do this.