How to keep a record with the highest value

maximus1974
maximus1974 used Ask the Experts™
on
I want to keep only records with the highest value when a duplicate exists. The column containing the highest value is named Revision and the column containing the duplicate is named Manual_ID.

Ex:

     Manual_ID               Revision
  22.11.20_A1234               4
  22.11.20_A1234               5

I want to keep only the record containing Revision 5.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
you can try:

select A.*
from YourTable as A
inner join (
   select manual_ID, max(revision) as Revision
   from YourTable 
   group by manual_ID
) AS B
on B.Manual_ID = A.Manual_ID
and B.Revision = A.Revision

Open in new window

If you want to delete the duplicates, then try this:

delete from tablname
 where revision not in
   (select max(revision) from tablname
     group by manual_id); 

Open in new window

Software Team Lead
Commented:
I think it can be as simplest as this if there's no other requirements:

select Manual_ID, Max(Revision) Revision
from yourTable
Group by Manual_ID

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial