Removing duplicates

Removing duplicates

• fdirosa

  Thanks for the suggestion it works
  
  

• Lucho1970

  Hi,
  
  I assume UserNo is unique and you want to keep the first of each user. You can determine the id of the first occurence of each UserName by finding the first id using MIN, like this: SELECT MIN(UserNo) FROM tablename GROUP BY UserName
  
  Now you selected the rows you do not want to be deleted, so you have to delete everything that is not in the resultset above, so that would look like this:
  DELETE FROM tablename
  WHERE UserNo NOT IN (SELECT MIN(UserNo) FROM tablename GROUP BY UserName)
  
  Good luck!
  
  

• Ostenda

  Here is one that would keep the first entry for each UserName.
  
  delete tb
  where UserNo not in(
  select min(UserNo)
  from tb
  group by UserName)
  
  
  
• Removing duplicates