SQL to delete duplicate records

Aleks
Aleks used Ask the Experts™
on
I have a table 'userlogin'  that has duplicates or tripiliates, even more for the  'loginid'  field. And we need to clean it up and leave only the most recent.

There are two relevant fields:

The 'userloginid'  is a consecutive number.  (Autoid), the higher the number the most recent the entry
And the 'Loginid' field which is the one we need to check for duplicates.

I need an SQL that will delete any entries that are duplicates (or more) and leave only one of those entries, leaving the one where the 'userloginid'  has the higher number (Most recent)

Any entries without duplicates should be left as is, we should not delete those. Only dup entries.

Any help is appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
Hi,

Take a look at this code, which I think does what you need.

Regards
  David
use EE
go

if object_id( N'tempdb..#UserLogin', N'U' ) is not null 
	drop table #UserLogin;
	
create table #UserLogin(
	UserLoginID int identity ( 1, 1 ) not null
	, LoginID int
	, LoginDateTime datetime not null default getdate()
	)
;
	
insert #UserLogin( LoginID )
	values( 2 )
		, ( 2 )
		, ( 1 )
		, ( 3 )
		, ( 1 )
;

select *
from #UserLogin
;

delete 
from #UserLogin
where
	UserLoginID not in
		(
		select
			max( UserLoginID )
		from #UserLogin
		group by
			LoginID
		)
;

select *
from #UserLogin
;

Open in new window

Analyst Developer
Distinguished Expert 2018
Commented:
You need to get the max userloginids then group them by the loginid field and finally delete the entries that not appear in this selection using not in statement like :
delete from
  userlogin
where
  userloginid not in (
    select
      max(userloginid)
    from
      userlogin
    GROUP BY
      loginid
   )

Open in new window

David ToddSenior Database Administrator

Commented:
Hi Zakaria,

Almost exactly what I posted,

Regards
  David
SharathData Engineer

Commented:
try this.
;WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Loginid ORDER BY userloginid DESC) rn 
  FROM your_table)
DELETE FROM CTE WHERE rn > 1

Open in new window

Author

Commented:
The first script did something but there were some duplicates left, the second one deleted all duplicates.
I will double check to make sure it only deleted duplicates, otherwise this was the simple and correct solution for what was needed.
I appreciate the input from all.

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