Hi
I have a table T with columns
Col1 Col2
1 A
2 B
3 C
4 D
5 D
6 A
7 B
8 C
9 C
10 A
11 B
12 C
13 C
14 B
15 A
....
....
I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.
So my desired output is:
Col1 Col2 Col3
1 A 1
2 B 1
3 C 1
4 D 1
5 D 2
6 A 2
7 B 2
8 C 2
9 C 3
10 A 3
11 B 3
12 C 4
13 C 5
14 B 5
15 A 5
....
....
Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.
I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this. Thanks.
Ramis