Delete duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete duplicates

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-05-07 : 06:39:38

Hi,

I have a table with duplicate values

Name JoinDate
abc 2007/01/05
abc 2007/01/04
def 2007/02/02
jik 2007/02/05
def 2007/02/06

I want the ouput as

Name JoinDate
abc 2007/01/05
def 2007/02/02
jik 2007/02/05

I want to keep the first entry of the user name and delete the other duplicates.

Can i do this in a single query??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 06:54:06

If you want "the first entry", you also must have some kind of audit mechanism.
A simple identity value will do, or a "registereddate" column. If you have not, this can't be done.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:56:47

[code]
Delete t1 From YourTable
Where
date =
(
Select Min(date) From yourTable t2 Where t1.Name = t2.Name
)
And Name In
(
Select Name From YourTable
Group by Name
Having Count(1) >1
)
[/code]

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:57:40

or May be this will be bit faster..


Delete t1 From YourTable 
Where 
date = 
	(
		Select Min(date) From yourTable t2 Where t1.Name = t2.Name
	)
And  Exists 
	(
		Select 1 From YourTable t2 Where t1.[Name ]= t2.[Name]
		Group by Name
		Having Count(1) >1 
	)

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 07:02:02

Neither of them meet OP requirement. See sample data and expected output.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 07:15:48

quote:
Originally posted by Peso

Neither of them meet OP requirement. See sample data and expected output.

Peter Larsson
Helsingborg, Sweden

Not Sure whether it typed it clearely output date..!!! but what i Assume is that, the OP wants keep the joining date which is the latest one... but which is not directed in this expected output..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 07:19:53

But if OP wants latest date, then you need max(JoinDate). Isn't it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 07:22:53

wants the latest date..., so i am deleting the min date..

Chirag

http://chirikworld.blogspot.com/

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-05-07 : 07:25:38

hi Guys,
Thanks for your replies
Sorry was away.

I was wondering what "t1" is , guess it is the alias for YourTable ??

The query was throwing error so tried this

Delete From YourTable t1
Where
date =
(
Select Min(date) From yourTable t2 Where t1.Name = t2.Name
)
And Exists
(
Select 1 From YourTable
Group by Name
Having Count(1) >1
)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 07:29:04

Opps there was some typo.. check this out.. !!


Declare @Table Table 
(
	[Name] Varchar(20),
	JoiningDate SmallDateTime
)

Insert @Table 
Select 'abc',' 2007/01/05' Union All
Select 'abc',' 2007/01/04' Union All
Select 'def',' 2007/02/02' Union All
Select 'jik',' 2007/02/05' Union All
Select 'def',' 2007/02/06'

--Query

Delete t1 From @Table t1  
Where 
JoiningDate = 
	(
		Select Min(JoiningDate) From @Table t2 Where t1.[Name ]= t2.[Name]
	)
And  Exists  
	(
		Select [Name] From @Table  t2  Where t1.[Name ]= t2.[Name]
		Group by Name
		Having Count(1) >1 
	)


Select * From @Table  

Name                 JoiningDate                                            
-------------------- ------------------------------------------------------ 
abc                  2007-01-05 00:00:00
jik                  2007-02-05 00:00:00
def                  2007-02-06 00:00:00

(3 row(s) affected)
--Output

Chirag

http://chirikworld.blogspot.com/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 07:32:36

He requires the first entry should be maintained and the remaining duplicates should be deleted.

As Peter Told we need a additional column which stores the last modified date of each row..unless we have that we will not get the required O/P.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 07:48:25

No, for ABC he wants MAX, and for DEF he wants MIN.
It is the order of entry he refers to.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 08:00:18

Aha.. then its not possible..!!!

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources