I accidently hijacked another thread with my own question and I apologize for that. I'll start a new one here.
I need help constructing a query that will read a column in a table and assign a unique value to the record (but not necessarily sequential) every time the current record does not equal the previous record. Below is a sample of data and what I'd expect the query to return. I've tried a few queries with PARTITION BY, but not having any luck getting the desired results.
Sample data set: Expected Result:
Branch_ID Branch_ID Group
1009 1009 1
1012 1012 2
1012 1012 2
1012 1012 2
1003 1003 3
1003 1003 3
1009 1009 4
1009 1009 4
1009 1009 4
1007 1007 5
1002 1002 6
dkyrtata suggested creating a virtual table with the following and joining it to the original table:
However, this solution won't solve my problem because of the fact that any Branch_ID can appear again in later rows. See 1009 in my example. By grouping on Branch_ID in a virtual table, I'm assigning one group_id to each unique Branch_ID. Instead, I need a unique group_id generated each time the Branch_ID changes from one row to the next.
I need help constructing a query that will read a column in a table and assign a unique value to the record (but not necessarily sequential) every time the current record does not equal the previous record. Below is a sample of data and what I'd expect the query to return. I've tried a few queries with PARTITION BY, but not having any luck getting the desired results.
Sample data set: Expected Result:
Branch_ID Branch_ID Group
1009 1009 1
1012 1012 2
1012 1012 2
1012 1012 2
1003 1003 3
1003 1003 3
1009 1009 4
1009 1009 4
1009 1009 4
1007 1007 5
1002 1002 6
dkyrtata suggested creating a virtual table with the following and joining it to the original table:
Code:
SELECT branch_id,MAX(ROWNUM) "group_id" FROM the_table GROUP BY branch_id;
However, this solution won't solve my problem because of the fact that any Branch_ID can appear again in later rows. See 1009 in my example. By grouping on Branch_ID in a virtual table, I'm assigning one group_id to each unique Branch_ID. Instead, I need a unique group_id generated each time the Branch_ID changes from one row to the next.