Htykuut

I want to complete the OPPOSITE of MS Access SQL: Get datasets with highest versions and revisions

I have a database table with (for this question) four columns:

1. ID


2. Document Number


3. Revison


4. Version

Each document has got 1..n Revisions and each Revision has got 1..n Versions.

What is my SQL statement for DELETING Documents where

• either the same Document No and the same revision, but a higher version exist


• 
  

  or the same Document No and a higher revision exist (including all versions)

  
  
  

  ID  Doc-No Rev Vers Should be deleted?
  
  1   Dok1   01  01   yes, because same Doc-No higher rev exists
  
  2   Dok1   01  02   yes, because same Doc-No higher rev exists
  
  3   Dok1   01  03   yes, because same Doc-No higher rev exists
  
  4   Dok1   02  01   yes, because same Doc-No and a higher vers for this rev exists
  
  5   Dok1   02  02   no, because Rev 02 / V02 is the highest for Doc-No Dok1
  
  6   Dok2   01  01   yes, because same Doc-No higher rev exists
  
  7   Dok2   02  01   yes, because same Doc-No higher rev exists
  
  8   Dok2   03  01   no, because Rev 03 / V01 is the highest for Doc-No Dok2
  
  

  
  

You can do:

delete from t

    where t.id <> (select top (1) t2.id

                   from t as t2

                   where t2.doc_no = t.doc_num

                   order by t2.version desc, t2.revision desc, t2.id desc

                  );

Of course, back up the table before trying such a delete.

The following should achieve the desired result:

delete from Table1 t1 

where exists

(

    select 1 from Table1 t2 

    where

    t1.[Doc-No] = t2.[Doc-No] and 

    (

        t1.Rev < t2.Rev or 

       (t1.Rev = t2.Rev and t1.Vers < t2.Vers)

   )

)

(Change Table to the name of your table along with any other fields which don't match your data)

Always backup your data before running delete queries - there is no undo!

Your original top revision and top version query would be more efficient with an INNER JOIN on a couple of aggregate queries for revision and version levels rather than a correlated subquery. The former calculates once for all rows in outer query and latter for every row:

SELECT d.*

FROM (documents d

INNER JOIN 

  (SELECT sub_d.[Doc-No], MAX(sub_d.Rev) AS max_rev

   FROM documents sub_d

   GROUP BY sub_d.[Doc-No]) AS agg1

ON d.[Doc-No] = agg1.[Doc-No]

AND d.Rev = agg1.max_rev)



INNER JOIN 

  (SELECT sub_d.[Doc-No], sub_d.Rev,

                          MAX(sub_d.Ver) AS max_ver

   FROM documents sub_d

   GROUP BY sub_d.[Doc-No], sub_d.Rev) AS agg2

ON d.[Doc-No] = agg2.[Doc-No]

AND d.Rev = agg2.rev    

AND d.Ver = agg2.max_ver

Which translates for DELETE as:

DELETE DISTINCTROW d.*

FROM (documents d

INNER JOIN 

  (SELECT sub_d.[Doc-No], MAX(sub_d.Rev) AS max_rev

   FROM documents sub_d

   GROUP BY sub_d.[Doc-No]) AS agg1

ON d.[Doc-No] = agg1.[Doc-No]

AND d.Rev = agg1.max_rev)



INNER JOIN 

  (SELECT sub_d.[Doc-No], sub_d.Rev,

                          MAX(sub_d.Ver) AS max_ver

   FROM documents sub_d

   GROUP BY sub_d.[Doc-No], sub_d.Rev) AS agg2

ON d.[Doc-No] = agg2.[Doc-No]

AND d.Rev = agg2.rev

AND d.Ver = agg2.max_ver