Removing Duplicates.

Here is the problem im trying to show Date and time for my selection, when i use the following code it shows the Date and time but leaves dups

SELECT DISTINCT(Item), OutDateTime_dt FROM Database Where AFTID_int = 2149 AND CID_int = 0 AND MID_int = 0 AND Item <> '' AND OutDateTime_dt >= '09/17/2003' AND OutDateTime_dt < '9/24/2003' Order by OutDateTime_dt Desc

However, if i remove the "OutDateTime_dt" from the first line,
leaving this

SELECT DISTINCT(Item) FROM Database

It removes the duplicates and works fine but doesnt give me the time as i need as well or date. Please help.If you only want one row per Item, and each Item may have many OutDateTime values, then you would have to choose which OutDateTime to display, e.g. the highest:

SELECT Item, MAX(OutDateTime_dt) FROM Database Where AFTID_int = 2149 AND CID_int = 0 AND MID_int = 0 AND Item <> '' AND OutDateTime_dt >= '09/17/2003' AND OutDateTime_dt < '9/24/2003' GROUP BY Item;

Removing Duplicates rows from Inner Join

i have two tables, tab1 having N1 col and tab2 N2 col. now N1 is subset
of N2.
I need the information from tab2 (having N2) of all rows having the
matching entry in N1 in tab1.
For this i am using Inner Join on cols N1 and n2. But result is giving
duplicate rows. Can anyone suggest how do u i remove those duplicate
rows? or may be a better way to do the above work... Thanks
Sounds like you should be using EXISTS
rather than a join

SELECT * FROM tab2
WHERE EXISTS (SELECT * FROM tab1 WHERE tab1.N1=tab2.N2)|||thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?|||Did you try to use DISTINCT to remove the duplicates?
SELECT DISTINCT tab2.* FROM tab2 INNER JOIN tab1 ON tab2.N2 = tab1.N1|||Can you post your DDL including indexes|||asgars (asgars@.gmail.com) writes:
> thanks mark
> but i have tables with large data and running this querry is taking a
> lot of time.
> can u suggest a better method or optimize this querry?

Which query? It's very difficult to suggest optimizations to a query
without seeing it, and without knowledge of the tables.

Please post:

o The query you are using now.
o CREATE TABLE and CREATE INDEX statements for the inolved tables.
o Some indication on number of rows in the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Removing duplicates

Hi all,
I have a table like
UserNo UserName
1 Ajit
2 Ajit
3 Ajit
4 Vishal
5 Sonu
6 Sonu
7 Ketan etc

And I want to remove all duplicates in this table any suggestions.Here is one that would keep the first entry for each UserName.
delete tb
where UserNo not in(
select min(UserNo)
from tb
group by UserName)|||Hi,

I assume UserNo is unique and you want to keep the first of each user. You can determine the id of the first occurence of each UserName by finding the first id using MIN, like this: SELECT MIN(UserNo) FROM tablename GROUP BY UserName

Now you selected the rows you do not want to be deleted, so you have to delete everything that is not in the resultset above, so that would look like this:
DELETE FROM tablename
WHERE UserNo NOT IN (SELECT MIN(UserNo) FROM tablename GROUP BY UserName)

Good luck!|||Thanks for the suggestion it works

Removing duplicates

Can somebody help me by telling the best way to remove duplicate rows from a table.You can query a table with SELECT DISTINCT to cause duplicate data to be omitted.

You could query the table in this way into a temporary table, empty the old table and repopulate it from the temporary.|||This depends on the structure of your table and what rows will be deleted and which will be kept. Are the entire rows identical (no unique identifier) ? How many records are in the table ?

removing duplicates

I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie YaegerAh - you need a "tiebreaker". Basically another column that you can
include in your selectivity criteria to make the rows unique. For
example, if it's feasible to assume that the same acctnum/invnum is
associated with only a single address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you
one of the dups. MIN() will give you a different dup. TOP 1 would
arbitrarily give you any 1 of the dups. It may be necessary to include
all the columns in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you
want more info. Itzik Ben-Gan has recently written about them in SQLMag
(http://www.windowsitpro.com/Article...5235/45235.html) if
you're a SQL Mag subscriber. But I'm sure a google search and/or a
google groups search would return many hits regarding tiebreakers in SQL
queries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bernie Yaeger wrote:

>I have a table with 15 columns. Among these are 2 text columns - acctnum
>and invnum. If any rows are the same, I want them removed. Thus,
>acctnum invnum cprice amtpaid address
>1234 33688 5.99 12.97 22 Hope Street
>9876 33688 4.22 1.97 27 Hope Street
>1234 33688 14.69 4.36 26 Hope Street
>5407 33688 3.47 8.89 25 Hope Street
>1234 00921 8.11 1107 24 Hope Street
>2970 33688 10.05 125.97 23 Hope Street
>Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
>3 should remain, but not both (if doesn't matter which is removed). If I
>select distinct and use the 'select...into' clause, I don't get all the
>other columns of information.
>Can someone help me with this?
>Thanks for any help.
>Bernie Yaeger
>
>|||Hi,
You can do this by setting rowcount. Try the following code:
SET ROWCOUNT 1
SELECT a.* FROM [tablename] a, [tablename] b
WHERE a.acctnum = b.acctnum
AND a.invnum = b.invnum
Now, although there are two different records (row 1 and row 3), the query
will return only one row.
Finally do this,
SET ROWCOUNT 0
"Mike Hodgson" wrote:

> Ah - you need a "tiebreaker". Basically another column that you can
> include in your selectivity criteria to make the rows unique. For
> example, if it's feasible to assume that the same acctnum/invnum is
> associated with only a single address then you could say
> select acctnum, invnum, cprice, amtpaid, address
> from mytable as a
> where address =
> (select max(b.address) from mytable as b
> where a.acctnum = b.acctnum
> and a.invnum = b.invnum)
> Any subquery that returns one of the dups would do. MAX() will give you
> one of the dups. MIN() will give you a different dup. TOP 1 would
> arbitrarily give you any 1 of the dups. It may be necessary to include
> all the columns in the select list to get a single acctnum/invnum result.
> There has been quite a bit written on tie-breakers on the web if you
> want more info. Itzik Ben-Gan has recently written about them in SQLMag
> (http://www.windowsitpro.com/Article...5235/45235.html) if
> you're a SQL Mag subscriber. But I'm sure a google search and/or a
> google groups search would return many hits regarding tiebreakers in SQL
> queries.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Bernie Yaeger wrote:
>
>|||Hi Bernie Yaeger,
I am not suggesting that following method is correct, but it surely
works.
Step 1 :- Create a table with select statement having identity column
select identity (1,1) myid,* into myTable from BaseTable
Step 2 :- Delete whatever rows you wish
delete from MyTable where myid in ( whatever)
Step 3 :- Remove the Identity column
alter table taa drop column myid
Step 4 :- Remove all records from Base Table
truncate BaseTable
Step 5:- Insert all records from MYTable
insert into BaseTable select * from MyTable
With regards
Jatinder|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column
(identity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Deeraj" <Deeraj@.discussions.microsoft.com> wrote in message
news:BD5EE2B3-E11D-4713-AA5F-F2D38433FF61@.microsoft.com...
> Hi,
> You can do this by setting rowcount. Try the following code:
> SET ROWCOUNT 1
> SELECT a.* FROM [tablename] a, [tablename] b
> WHERE a.acctnum = b.acctnum
> AND a.invnum = b.invnum
> Now, although there are two different records (row 1 and row 3), the query
> will return only one row.
> Finally do this,
> SET ROWCOUNT 0
> "Mike Hodgson" wrote:
>|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger

Removing duplicates

Hi

I have inherited a web app with the following table structure, and need to
produce a table without any duplicates. Email seems like the best unique
identifier - so only one of each e-mail address should be in the table.

Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able to
get a duplicate count working:

select Email, count(*) as UserCount
from dbo.Members
group by Email
having count(*) > 1
order by UserCount desc

But the methods for create a new table without duplicates fail. My code for
the 2nd method is:

sp_rename 'Members', 'temp_Members'

select distinct *
into Members
from temp_Members

Table...

CREATE TABLE [dbo].[Members] (
[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[SendMail] [smallint] NOT NULL
) ON [PRIMARY]
GO

Thanks B.Can I assume that memberid is unique? If so, try this:

INSERT INTO NewTable
(username, password, email, title, firstname, surname, address1,
address2, city, country, profession, publication, dateadded, sendmail)
SELECT username, password, email, title, firstname, surname, address1,
address2, city, country, profession, publication, dateadded, sendmail
FROM Members AS M1
JOIN
(SELECT MIN(memberid) AS memberid
FROM Members
GROUP BY email) AS M2
ON M1.memberid = M2.memberid

--
David Portas
----
Please reply only to the newsgroup
--|||Microsoft has a great article on this at
http://support.microsoft.com/defaul...4&Product=sql2k

I use a code block for doing this that I load as required, I only run
it when I have to. Once you've cleaned up your data, put a unique
constraint to prevent dupes from getting back in.

This is the code that I use for cleaning up dupes, you'll have to
modify the table and field names obviously. This config assumes you
are allowing Select/Into's.

/*
--
--WARNING! EXECUTE THIS SCRIPT WITH EXTREME CAUTION!,
--AND THEN ONLY ONE STEP AT A TIME!
--
--In the TotalTransit (DDS) database, this needs to run against
--ddsTrip and ttVoucher_Trip. Since the two tables have different
--layouts, the table that holds dupe rows must be dropped and
--recreated every time this process is run.
--
--There are now two seperate scripts, one for each table.
--
--Run the process one step at a time by highlighting each step.
--
--Wayne West, 10/13/03
-- WW 10/17/03 -- split into two procs, added more comments
--
------------------------
--
--this is here to help find if there are any dupes
select tripid, count(*)
from ddsTrip
group by tripid
having count(*) > 1

select tripid, count(*)
from ttVoucher_Trip
group by tripid
having count(*) > 1
--
------------------------
*/
/*
--Step 1 -- Verify there are dupes in the table
select tripid, count(*)
from ddsTrip
group by tripid
having count(*) > 1

--Step 2a -- Drop table that will hold dupe key values
drop table zzzholdkey

--Step 2b -- Collect key values, save in zzzHoldKey
select RecKey = tripid, KeyCount = count(*)
into zzzHoldKey
from ddsTrip
group by tripid
having count(*) > 1

--Step 3a -- Drop table that will hold one instance of duplicate rows
drop table zzzholddupes

--Step 3b -- Collect one instance of duplicate rows
select DISTINCT t.*
into zzzHoldDupes
from ddsTrip t, zzzholdkey hld
where t.tripid = hld.reckey

--Step 4 -- See if more than one field in addition to TripID is
duplicated
--This will indicate additional steps must be taken to clean up the
table.
select count(*), tripid
from zzzholddupes
group by tripid
having count(*) > 1

--Step 5 -- Delete ALL rows based on key value of duplicate records
delete ddsTrip
from ddsTrip t, zzzholdkey hld
where t.tripid = hld.reckey

--Step 6 -- Reinsert the row captured in Step 3b
insert ddsTrip
select *
from zzzholddupes

--You probably ought to rerun Step 1 to make sure the file is clean.
*/

removing duplicate rows

I am not able to figure out a script that removes duplicates if for the same
SSNs, the startdate and a code is the same.
sample data
SSN startdate code
123456789 11-12-1980 2540
456789123 14-5-1965 1236
123456789 02-09-1980 7456
789456132 06-07-1950 2589
123456789 11-12-1980 2540
What I want deleted is here is :
123456789 11-12-1980 2540 (just one of the two records)
Any help would be great
thanksAs long as you don=B4t have any identifier which can differ between the
rows there are onl workarounds for that:
1=2E Include a identifier to differ (like an identity column), then use
the follwing code:
DELETE YourTable FROM YourTable T
INNER JOIN
(
SELECT identifiercolumn from YourTable
GROUP BY SSN,startdate,code
HAVING COUNT(*) > 1
) SuQUery
ON SubQuery.identifiercolumn =3D T.identifiercolumn
2=2E (Preferable one) Copy one of the duples to a temp table, delete all
duplicates and reinsert the data from the temp table.
3=2E Avoid duplicates !
HTH, Jens Suessmeyer.|||Jens
There is a sequence number in the table that is unique. But It will not
matter which one we delete I would prefer the number that has a greater valu
e
(as in the seqnumber). Also I am pretty new to programming in SQL so if
possible can u actually show me how to crerate an identifier Copy one of the
duples to a temp table, delete all duplicates and reinsert the data from the
temp table.And avoid duplicates !
Thanks for your help
Amit
"Jens" wrote:

> As long as you don′t have any identifier which can differ between the
> rows there are onl workarounds for that:
> 1. Include a identifier to differ (like an identity column), then use
> the follwing code:
> DELETE YourTable FROM YourTable T
> INNER JOIN
> (
> SELECT identifiercolumn from YourTable
> GROUP BY SSN,startdate,code
> HAVING COUNT(*) > 1
> ) SuQUery
> ON SubQuery.identifiercolumn = T.identifiercolumn
> 2. (Preferable one) Copy one of the duples to a temp table, delete all
> duplicates and reinsert the data from the temp table.
> 3. Avoid duplicates !
> HTH, Jens Suessmeyer.
>|||Hi There,
Having a table with all column values identical for two or more rows
means that there are no constraints . Are you trying to manage history
and usable data in the same table?
I Think this query might help you.
Delete from YourTableName Where RowID NOT IN (Select MIN(ROWID) From
YourTableName Group By FieldList,......)
The group by will have all the fields except ROWID (or Unique
identifier)
I still say Aviod duplicates and have constraints on the table . Manage
history and usable data in different table (if this is the case).
With Warm regards
Jatinder SIngh

Removing duplicate data

I have a table that I need to remove duplicates from and the table includes
an identity column. The table contains 12 fields but our business rules are
that only 3 of the fields can make the record a duplicate. We would like to
keep the record with the min identity column. I have done this in the past,
but it was about 5 years ago and I did not insert the dups into another
table, I did it strictly with a query. Can anybody please help me out with
some code to take care of this or any suggestions on a better way of doing
this? So something along the lines of grouping the data by the fields that
we are interested in and then deleting the records where the identity column
is greater than the min identity column.
Thanks.This is the duplicates along with the
minimum id per group
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1
Join this to the original table
to give the ids of the duplicates
excluding the minimum id.
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
Put it all together to delete them
DELETE
FROM sometable
WHERE id IN (
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
)|||Do you need to export the removed duplicates to another table?
Try something like this:
INSERT INTO RemDup (...) SELECT ... FROM MyTab AS t1 WHERE EXISTS (SELECT
NULL FROM MyTab AS t2 WHERE t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND
t1.Col2=t2.Col2 AND t1.ID>t2.ID)
DELETE FROM MyTab AS t1 WHERE EXISTS (SELECT NULL FROM MyTab AS t2 WHERE
t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND t1.Col2=t2.Col2 AND t1.ID>t2.ID)
HTH,
Axel Dahmen
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
> I have a table that I need to remove duplicates from and the table
includes
> an identity column. The table contains 12 fields but our business rules
are
> that only 3 of the fields can make the record a duplicate. We would like
to
> keep the record with the min identity column. I have done this in the
past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
that
> we are interested in and then deleting the records where the identity
column
> is greater than the min identity column.
> Thanks.|||Here's an example. This will delete all but 1 of the dupes. You will need to
change table / col name accordingly
DELETE FROM _Holding_table
WHERE EXISTS(SELECT NULL FROM _Holding_table s1
WHERE s1.PhoneNumber= _Holding_table.PhoneNumber
and s1.PK_Holding_TableID > _Holding_table.PK_Holding_TableID)
HTH. Ryan
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
>I have a table that I need to remove duplicates from and the table includes
> an identity column. The table contains 12 fields but our business rules
> are
> that only 3 of the fields can make the record a duplicate. We would like
> to
> keep the record with the min identity column. I have done this in the
> past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
> with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
> that
> we are interested in and then deleting the records where the identity
> column
> is greater than the min identity column.
> Thanks.|||This is untested, perhaps you wrap this in a transaction first:
DELETE FROM SomeTable S
WHERE Idcolumn NOT IN
(
SELECT MIN(idcolumn)
FROM SOMETABLE S2
GROUP BY FirstColumn1,FirstColumn2,FirstColumn3
)
HTH, Jens Suessmeyer.