Generate unique values based on change from previous record 1

[aks12]

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:

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.

 

[dkyrtata]

I see your problem. I did not notice that detail. Your problem is a lot more challenging now.

Perhaps the OLAP function, LAG(), can help. LAG() and LEAD() return the previous/next value of a column.

I never used these functions, because, I find all the OLAP functions very time-consuming to figure out.

I do have one concern however. The result of the solution depends on the ordering of the output. But SQL never guarantees the ordering of records unless you sort them. If you don't sort them, your results can potentially change as records are added and deleted from the table.

 

[Dagon]

You seem to have 1009 assigned to both 1 and 4 in your data, which I assume is an error. This is an approach you could take.

SQL> create table branches (branch_id number, groupno number)
Table created.
SQL> insert into branches (branch_id) values (1009 )
1 row created.
SQL> insert into branches (branch_id) values (1012   )
1 row created.
SQL> insert into branches (branch_id) values (1012 )
1 row created.
SQL> insert into branches (branch_id) values (1012 )
1 row created.
SQL> insert into branches (branch_id) values (1003 )
1 row created.
SQL> insert into branches (branch_id) values (1003 )
1 row created.
SQL> insert into branches (branch_id) values (1009 )
1 row created.
SQL> insert into branches (branch_id) values (1009 )
1 row created.
SQL> insert into branches (branch_id) values (1009 )
1 row created.
SQL> insert into branches (branch_id) values (1007 )
1 row created.
SQL> insert into branches (branch_id) values (1002 )
1 row created.
SQL> update branches b
set groupno = 
(select b1.rnk
from (select rnk, rowid
        from
        (select branch_id, dense_rank() over (order by branch_id) rnk
         from branches)) b1
where b.rowid = b1.rowid)
11 rows updated.
SQL> select * from branches order by branch_id

 BRANCH_ID    GROUPNO
---------- ----------
      1002          1
      1003          2
      1003          2
      1007          3
      1009          4
      1009          4
      1009          4
      1009          4
      1012          5
      1012          5
      1012          5

11 rows selected.

For Oracle-related work, contact me through Linked-In.

 

[ChrisHunt]

How do you determine the order in which your branch_ids appear in?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Dagon]

Sorry, I didn't read the later part of your question properly. As ChrisHunt says, you need some rule to say why there is a 1009 at the start of the list that is grouped differently to the 1009s at the end of the list. If it is simply the order in which rows appear in the table when you do select * from table, that is not going to work. You need something like a date or sequence number in the table to specify the ordering.

For Oracle-related work, contact me through Linked-In.

 

[taupirho]

This is trickier than it looks if just using SQL. This was as close as I could get without spending too much time on it. As you are on Oracle10 a better and more complete solution could probably be done using the MODEL clause.

select * from tom
/

BRANCH_ID
----------
1009
1012
1012
1012
1003
1003
1009
1009
1009
1007
1002

11 rows selected.

select branch_id,max(id) over(order by rid)
from
(
select branch_id,case when id = 0 then null else id end id ,rid
from
(
select branch_id,(SIGN(ABS(branch_id-nvl(nb,0))) *rid) id,
rid from
(
select branch_id ,
lag(branch_id) over(order by rownum) nb,
dense_rank() over( order by branch_id), rownum rid
from tom
) order by rid
)
)
/

BRANCH_ID MAX(ID)OVER(ORDERBYRID)
---------- -----------------------
1009 1
1012 2
1012 2
1012 2
1003 5
1003 5
1009 7
1009 7
1009 7
1007 10
1002 11

11 rows selected.



In order to understand recursion, you must first understand recursion.

 

[aks12]

taupirho, your solution did the trick for me. I should have explained in my original post that my table was ordered on a customer name which is why Branch_ID could be repeated in later rows. My ultimate purpose is to create a report where I can force a page break after each change of Branch_ID, something I couldn't do without the derived group column.