Compare Clob and assign identifier for unique clob contents

I have a table with clob contents, I need to compare clob contents and assign each distinct clob content a sequence number grouped by a clob_num.

Expected results from the test data below:

123, 5555,5/5/2015,'This is a test', 1
'124', '5555','5/2/2015','This is a test',1
'125', '5555','5/3/2015','This is a test',1
'126', '5555','5/5/2015','This is a test and a hard one too',2
127', '5556','5/5/2015','This is a test and another one',3
128', '5556','5/4/2015','This is a test and a hard one too',4
'129', '5556','5/5/2015','This is a test and a hard one too',4

so If I ran a query to group them together it will return
Clob_num, Clob_seq, count
5555,1,3
5556 2,1
5556,3,1
5556, 4,2

Test Data
Create table tab1 (clob_pk varchar2(3),clob_num varchar2(4),clob_date date, clob_contents clob);

insert all into tab1 values ('123', '5555','5/5/2015','This is a test')
into tab1 values ('124', '5555','5/2/2015','This is a test')
into tab1 values ('125', '5555','5/3/2015','This is a test')
into tab1 values ('126', '5555','5/5/2015','This is a test and a hard one too')
into tab1 values ('127', '5556','5/5/2015','This is a test and another one')
into tab1 values ('128', '5556','5/4/2015','This is a test and a hard one too')
into tab1 values ('129', '5556','5/5/2015','This is a test and a hard one too')
select * from dual;

alter table tab1 add column (clob_seq varchar2(10));

Open in new window

DBAnewbie77Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The compare has been answered in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28668074.html#a40760543

Maybe change row_number to rank?

Then a simple count.
slightwv (䄆 Netminder) Commented:
Does it matter what row gets what sequence?

Try this:
select clob_pk, dense_rank() over(order by clob_num, hash) rnk
from (
select clob_pk, clob_num,
	dbms_crypto.hash(clob_contents,2) hash
from tab1
)
order by 2,1
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBAnewbie77Author Commented:
Yes, they want every row to have a number corresponding to its clob contents. Your suggestions do work for the final part of problem, thanks for that, but I still need to update with a sequence.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>>but I still need to update with a sequence.

It looks like every row just gets the next number:
Create an Oracle sequence and use your_sequence.nextval in the update statement.
DBAnewbie77Author Commented:
>>It looks like every row just gets the next number:
>>Create an Oracle sequence and use your_sequence.nextval in the update statement.

I may be doing something wrong, but I use the update statement and replace with rank, it only updates the rows that have a ranking higher than 1 of course because of the where clause. I need to update every row regardless of ranking, just based off clob contents so each distinct clob has a corresponding id num.

Update Statement
update tab1 set clob_sequence=clob.nextval
where clob_pk in (
select clob_pk from (
select clob_pk,
	rank() over(partition by clob_num, dbms_crypto.hash(clob_contents,2) order by clob_date asc) rn
from tab1
) where rn > 1
)

Open in new window

slightwv (䄆 Netminder) Commented:
Check the where clause:
...
) where rn > 1
...

rn is the rank.

Also note I used dense_rank and my partition by is different.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.