Removing duplicate rows

I need to remove duplicate rows that are not exactly
identical in all columns. Only if a row has three columns
that are identical to another, I want to remove it. In my
example I'm looking for Account_Number, Check_Number,
Check_Amount. Is there a Query that can do this?
LeeWithout seeing the DDL it's impossible to give a precise answer. If you have
a single-column primary key (Keycol in this example) and don't mind which of
the duplicate rows get removed:
DELETE FROM Sometable
WHERE keycol NOT IN
(SELECT MIN(keycol)
FROM Sometable
GROUP BY account_number, check_number, check_amount)
--
David Portas
--
Please reply only to the newsgroup
--
"Lee Surma" <lee@.honeycomb.net> wrote in message
news:018101c36b3c$f66dd9f0$a601280a@.phx.gbl...
> I need to remove duplicate rows that are not exactly
> identical in all columns. Only if a row has three columns
> that are identical to another, I want to remove it. In my
> example I'm looking for Account_Number, Check_Number,
> Check_Amount. Is there a Query that can do this?
> Lee
>
>|||This might help you:
http://www.sql-server-
performance.com/rd_delete_duplicates.asp
How big is the table?
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>|||More, this one from Microsoft:
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q1
39/4/44.asp&NoWebContent=1
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>

Removing duplicate dat

Hi
I have a 1.5 million row table with a text field. Can
anybody think of a way to remove any duplicate rows where the text field
contains the same data (and a
way which will not take days to run!)
For example if I have two rows with "SQL SERVER IS GREAT", I want to remove
one of them.
Thanks
here is an example of something that works for me:
create table textstuff
(pk int not null primary key,
textcol text)
go
--insert statements
declare @.int int
select @.int=max(datalength(textcol)) from textstuff
select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
textstuff order by 2
select pk, holding.checksum from holding,
(select checksum, test=count(checksum) from holding group by checksum having
count(checksum) >1) as a
where holding.checksum=a.checksum
rows which have identical checksums will show up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <xx@.nospam.com> wrote in message
news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a 1.5 million row table with a text field. Can
> anybody think of a way to remove any duplicate rows where the text field
> contains the same data (and a
> way which will not take days to run!)
> For example if I have two rows with "SQL SERVER IS GREAT", I want to
remove
> one of them.
> Thanks
>
>
|||That's awsome, thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u4beTGwuEHA.3808@.TK2MSFTNGP15.phx.gbl...
> here is an example of something that works for me:
> create table textstuff
> (pk int not null primary key,
> textcol text)
> go
> --insert statements
> declare @.int int
> select @.int=max(datalength(textcol)) from textstuff
> select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
> textstuff order by 2
> select pk, holding.checksum from holding,
> (select checksum, test=count(checksum) from holding group by checksum
having
> count(checksum) >1) as a
> where holding.checksum=a.checksum
>
> rows which have identical checksums will show up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Paul" <xx@.nospam.com> wrote in message
> news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> remove
>