Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Grouping Non Consecutive Rows

433185Aug 23 2012 — edited Aug 28 2012
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
This post has been answered by Solomon Yakobson on Aug 26 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 25 2012
Added on Aug 23 2012
45 comments
1,589 views