asked on

Finding the most recent document rev number

Hello,
I have 3 records where I want to remove any duplicates and just choose the 'highest' doc_rev_nbr.
Like with the doc_rev_nbr = 'K' would be the one to keep.
I tried 2 different queries and they pick the highest rowid and sometimes the rowid would not be the
highest doc_rev_nbr.
Is there a way just to have the query pick the highest doc_rev_nbr and not worry about the rowid or
any other column to check or compare against, or is this not possible.
Thanks.

--2 separate queries tried below...
delete from documents a
where a.rowid
(select max(b.rowid)
from documents b
where a.Config_Id = b.Config_Id
and a.Doc_Type_Cd = b.Doc_Type_Cd
and a.Doc_Nbr = b.Doc_Nbr);

delete from documents a
where a.rowid not in
(select max(b.rowid)
from documents b
group by b.Config_Id, b.Doc_Type_Cd, b.Doc_Nbr);

Oracle 11g used.

slightwv (䄆 Netminder)

See if this returns all the 'duplicates':
select * from (
select rowid myid, row_number() over(partition by Config_Id, b.Doc_Type_Cd order by doc_rev_nbr desc) rn
from documents
) where rn>2

If so:
delete from documents where rowid in
(
select myid from (
select rowid myid, row_number() over(partition by Config_Id, b.Doc_Type_Cd order by doc_rev_nbr desc) rn
from documents
) where rn>2
)

PortletPaul

Of the 3 records shown, you only mention keeping "K" revision, they all share the same Config_Id but there are 2 different Doc_Type_Cd values.

If you do not care what the value is in Doc_Type_Cd, then the row_number() should be used without referencing that column in the partition.

select * from (
select rowid myid, row_number() over(partition by Config_Id order by doc_rev_nbr DESC) as rn
from documents
)
where rn > 1

notes:
the descending order is vital to retain "the highest"
not sure why slightwv suggested using rn > 2

Steve A

ASKER

thanks for the replies and information. I tried this and gave me an error:
SQL Error: ORA-01732: data manipulation operation not legal on this view

delete from (
select rowid myid, row_number() over(partition by Config_Item_Id order by document_rev_nbr DESC) as rn
from documents
)
where rn > 1

where I want to now remove these rows and keep the 'K' doc_rev_nbr row only.

Thanks

slightwv (䄆 Netminder)

>> not sure why slightwv suggested using rn > 2

Not sure myself. Typo?

>>SQL Error: ORA-01732: data manipulation operation not legal on this view

You'll need to perform the deletes on the base tables not the view.

You can find the view text with:
set long 10000000
select text from all_views where view_name='DOCUMENTS';

The "set long" is for sqlplus and compatible tools because text is a CLOB.

Steve A

ASKER

Thanks for the information. This is not a view but a base table as mentioned.

select * from all_tables where table_name = 'DOCUMENTS'; --this returns a row...

select text from all_views where view_name='DOCUMENTS'; --this returns no rows...

slightwv (䄆 Netminder)

Sorry. I missed the obvious. Your delete from with only a select, is using an inline view.

Instead of:
delete from (

You delete from a table name.

Notice the delete I posted:
delete from documents where rowid in
( select ...

If you can post the data as text along with expected results, I'll set up a test case and post working code.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve A

ASKER

Tried the code and it works great! Thanks again.