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.


Capture.PNG

--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.
MachinegunnerAsked:
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:
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
)
PortletPaulEE Topic AdvisorCommented:
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
MachinegunnerAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
>>   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.
MachinegunnerAuthor Commented:
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) Commented:
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.
slightwv (䄆 Netminder) Commented:
I went ahead and rekeyed all the values.

Here is the complete test case (with the RN correction pointed out above):
--drop table tab1 purge;
create table tab1(item_id number, config_id number, doc_type_cd char(1), doc_nbr varchar2(10), doc_rev_nbr char(1), doc_use_dt date);

insert into tab1 values(104103899,1953362,'A','TDxxx','B',to_date('30-Nov-05','DD-Mon-YY'));
insert into tab1 values(104103901,1953362,'P','GMxxx','K',to_date('9-Dec-05','DD-Mon-YY'));
insert into tab1 values(104103900,1953362,'P','TSxxx','C',to_date('6-Dec-05','DD-Mon-YY'));
commit;

delete from tab1 where rowid in
 (
 select myid from (
 select rowid myid, row_number() over(partition by Config_Id order by doc_rev_nbr desc) rn
 from tab1 
 ) where rn>1
) 
/

select * from tab1;

   ITEM_ID  CONFIG_ID DOC DOC_NBR                        DOC DOC_USE_DT
---------- ---------- --- ------------------------------ --- -------------------
 104103901    1953362 P   GMxxx                          K   12/09/2005 00:00:00

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
MachinegunnerAuthor Commented:
Tried the code and it works great!  Thanks again.
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.