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.
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
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

Zakaria AcharkiAnalyst DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:
Hi Zakaria,

Almost exactly what I posted,

Regards
  David
SharathData EngineerCommented:
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

AleksAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.