Substituing Duplicacy..... - 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

General SQL Server Forums

New to SQL Server Programming

Substituing Duplicacy.....

Author

Topic

rd_innovatives
Starting Member

18 Posts

Posted - 2006-10-30 : 08:01:25

Hi All,

Greetings .....
I am wondering if you can answer me this question ...
suppose ... a table contains rows and and many rows are duplicate ..
I want to store only the UNIQUE rows in the table ...
I dont want to use any temp. table , cursor etccc for that activity ...
Can you please help me ....
Thanks in Advance :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 08:04:33

For this homework, I write

delete		mt
from		mytable mt
inner join	(
			select		mycolumn,
			from		mytable
			group by	mycolumn
			having		count(*) > 1
		) q on q.mycolumn = mt.mycolumn

Peter Larsson
Helsingborg, Sweden

rd_innovatives
Starting Member

18 Posts

Posted - 2006-10-30 : 08:05:55

Thanks peter .. will try this workout :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-30 : 08:57:58

Umh...wouldn't this delete all the duplicates and leave no rows behind?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 09:06:05

not the rows where count(*) = 1

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-30 : 10:09:06

It will kill ALL the dupe rows and not leave one. I would think that you would want to leave ONE of the dupes.



create table mytable (foo int identity(1,1),bar varchar(10))

insert into mytable(bar)

select 'One' union all
select 'One' union all
select 'One' union all
select 'Two' union all
select 'Three' union all
select 'Three'


select * from mytable 

delete		mt
from		mytable mt
inner join	(
			select		bar
			from		mytable
			group by	bar
			having		count(*) > 1
		) q on q.bar = mt.bar


select * from mytable

Results:
Foo Bar
4 Two

I would expect the results:
Foo Bar
1 One
4 Two
5 Three

EDIT: my preview isnt working. sorry about the formatting

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-30 : 10:16:49

Ok here is a quick and dirty one. Perhaps our resident saint can clean it up



create table mytable (foo int identity(1,1),bar varchar(10))

insert into mytable(bar)

select 'One' union all
select 'One' union all
select 'One' union all
select 'Two' union all
select 'Three' union all
select 'Three'


select * from mytable 

WHILE EXISTS(
		SELECT
			MAX(Foo) AS Foo,Bar
		FROM 
			mytable
		GROUP by 
			Bar
		HAVING COUNT(*) > 1
		)

BEGIN
	DELETE FROM mytable
	
	WHERE Foo IN
	
	(SELECT Foo FROM
		(SELECT 
			MAX(Foo) AS Foo,Bar
		FROM 
			mytable
		GROUP BY 
			Bar
		HAVING COUNT(*) > 1
		)q 
	)
END

select * from mytable

drop table mytable

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 10:35:44

Also refer to here http://www.sqlteam.com/item.asp?ItemID=3331

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-30 : 11:20:05

oh hell...

select col1, col2, coln
from
(
select col1, col2, coln
from myTable
union
select col1, col2, coln
from myTable
) t1

this will select only one of the dupes. it won't remove them.

if you want to remove the duplicates then do:
select col1, col2, coln
into #temp
from
(
select col1, col2, coln
from myTable
union
select col1, col2, coln
from myTable
) t1
truncate table myTable
isnert into myTable(col1, col2, coln )
select col1, col2, coln
from #temp

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 11:22:07

Even if OP writes following

quote:
I want to store only the UNIQUE rows in the table ...

Wouldn't he have written

quote:
I want to store only the DISTINCT rows in the table

if following Lumbago and DonAtWork minds?

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-30 : 11:26:31

I suppose you can read it that way. However, I am used to dealing with USERS at work. I translate what they ask for into something that makes sense. You took him literally. I would have to say you gave him what he asked for. Now, pretty please compact my code into something nice.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-30 : 11:27:45

that could swing both ways depending on the OP's knowledge of the language
to the lack of proper techical expresions.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 11:29:57

I am too old and too tired to play games. If OP doesn't care to provide more detailed information, he gets what he asks for.
After all, this forum is free of charge.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-30 : 11:32:13

hehe just 15 posts to go before you start seeing the light, peter.