Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

SQL to delete duplicate records

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.
Avatar of David Todd
David Todd
Flag of New Zealand image

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

ASKER CERTIFIED SOLUTION
Avatar of Zakaria Acharki
Zakaria Acharki
Flag of Morocco image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Zakaria,

Almost exactly what I posted,

Regards
  David
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

Avatar of Aleks

ASKER

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.