Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199sql

duplicates

How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.See if this helps:
http://support.microsoft.com/defaul...kb;en-us;139444
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
How do I delete duplicate entries?
For example, lets says I have the following table format:
SystemName, Memory, CPU
I would like to delete any duplicates that are in SystemName.|||Please refer to www.aspfaq.com/5006 and provide sufficient information for
others to better understand you problem.
Deletes are applicable to a set of rows, not individual columns. If the
entire row is duplicated, then use the link posted by Vyas & make sure you
explicitly declare a primary key to avoid further duplication. If only the
sytemname is duplicated, they you will have to decide which values of memory
and/or cpu should be preserved and which ones should be deleted. Generally,
one can apply an extrema aggregate like MIN or MAX like:
SELECT *
FROM tbl t1
WHERE t1.Memory = ( SELECT MAX( t2.memory ) FROM tbl t2
WHERE t2.system = t1.system )
You can convert this into a delete like:
DELETE FROM tbl
WHERE Memory = ( SELECT MAX( t1.memory ) FROM tbl t1
WHERE tbl.system = t1.system )
If the duplicated values are determined by memory and/or cpu, you can use
WHERE Memory + '!' + cpu = ( SELECT MAX( t1.Memory + '!' + t1.cpu ) ...
Another way of writing it would be
DELETE FROM tbl
WHERE EXISTS ( SELECT * FROM tbl t1
WHERE t1.Memory > tbl.Memory
OR t1.cpu > tbl.cpu
OR ... )
Anith|||Of course, once you have duplicates purged, you should implement a unique
key constraint to prevent this from occurring again. But to answer your
question, let's say you have a table called Accounts and the column
combination tran_type should be unique. The following query will list all
records that violate the non-unique rule. We are basically joining back to a
group by sub-query having count(*) > 1. You can modify this as needed to fit
your situation. Once done, you can better profile the records to determine
which should be deleted. For example, do you want to keep the first entered
or last entered, or perhaps duplicates entered during a specific time frame
should be deleted.
select
Accounts.*
from
Accounts
join
(
select
account,
tran_type,
count(*) as cnt
from
Accounts
group by
account,
tran_type
having
count(*) > 1
) as x
on x.account= Accounts.account and
x.tran_type= Accounts.tran_type
order by
NSA.account,
NSA.tran_type
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:O8KImBgYFHA.2948@.TK2MSFTNGP10.phx.gbl...
> How do I delete duplicate entries?
> For example, lets says I have the following table format:
> SystemName, Memory, CPU
> I would like to delete any duplicates that are in SystemName.
>
>

duplicates

hi, here i am giving details about problem,please help me

table a
---
Std_id Std_name Grade Subject Score year
----------------
1 name1 grade3 mathematics 200 200102
2 name2 grade5 science 150 200001
1 name1 grade3 science 300 200102
3 name3 grade4 english 500 200203
4 name4 grade6 social studies 350 200304
5 name5 grade8 history 440 200405

table b
---
subject year
-------
mathematics 200304
science 200304
english 200304
social studies 200304
history 200304
mathematics 200405
science 200405
english 200405
social studies 200405
history 200405

query 1 is
select a.std_id,a.std_name,sum(a.score),b.-- from a,b
where a.subject=b.subject
group by Std_name,....

out put is
std_id std_name sum(score) .....
----------
1 name1 1000
2 name2 300
3 name3 1000
4 name4 700
5 name5 880

but actual output should be like
std_id std_name sum(score)
----------
1 name1 500
2 name2 150
3 name3 500
4 name4 350
5 name5 440

to get this output i have modified the table b and query then i got the right results

table b
---
mathematics 200001
science 200001
english 200001
social studies 200001
history 200001
mathematics 200102
science 200102
english 200102
social studies 200102
history 200102
mathematics 200203
science 200203
english 200203
social studies 200203
history 200203
mathematics 200304
science 200304
english 200304
social studies 200304
history 200304
mathematics 200405
science 200405
english 200405
social studies 200405
history 200405

query is

select a.std_id,a.std_name,sum(a.score),b.-- from a,b
where a.subject=b.subject and
a.year=b.year
group by std_name,....

now actual and expecting results are same
std_id std_name sum(score)
----------
1 name1 500
2 name2 150
3 name3 500
4 name4 350
5 name5 440

so my question comes here
is there any other way which i can get the same out put without adding rows(data) to table b,
please help me out of this problem.

thank you
pracalus27 views no suggestions.please somebody give me suggestion to resolve this problem

thank you
pracalus|||Try this

select a.std_id,a.std_name,sum(a.score),b.-- from a,b
where a.subject=b.subject and a.year=b.year
group by Std_name,....

Otherwise Post your query1 fully|||Hi Madhi,

thank you, you said use year=year then how it can pick the 20001,200102 and 200203 years data.it only fetch 200304 and 200405 years.
table a table b
200001
200102
200203
200304 200304
200405 200405

thank you
pracalus

Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199

Duplicates

Hi all,
This works fine to show duplicates of NumbB Column. I want to show the
duplicates and = different lastnames with those that
are duplicates.
Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
where NumbB in(select NumbB from PC
group by NumbB
having count(NumbB)>1)order by NumbB
thanks
GVOne general approach is:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.col <= t1.col ) >= 2 ;
As a side note, for such questions, always post table DDLs & sample data
along with expected results so that others can understand your requirements
better.
Anith|||> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
Can you rephrase what you want?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"gv" wrote:

> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>
>|||Hi again and thanks
Ok, thanks for your help
I want to pull duplicates of Col2, order them by Col2 and were
Col3(lastname) is the same within the duplicates.
SELECT Col1,Col2,Col3,Col4,Col5
FROM Table1
WHERE Col2 IN(SELECT Col2 FROM Table1
GROUP BY Col2
HAVING COUNT(Col2)>1)
ORDER BY Col2
Sample rows
Col1 Col2 Col3 Col4 Col5
532 000 doe J Intraductal EUS
8675 000 loe S Gastric
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
836 632 QQQ F Gastric
So would like the return to be:
Col1 Col2 Col3 Col4 Col5
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
thanks
GV
"gv" <viatorg@.musc.edu> wrote in message
news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>|||Try (not tested),
SELECT a.Col1,a.Col2,a.Col3,a.Col4,a.Col5
FROM
Table1 as a
inner join
(
SELECT Col2, col5
FROM Table1
GROUP BY Col2, col5
HAVING COUNT(*) > 1
) as b
on a.col2 = b.col2 and a.col5 = b.col5
order by
a.col2, a.col5
AMB
"gv" wrote:

> Hi again and thanks
>
> Ok, thanks for your help
> I want to pull duplicates of Col2, order them by Col2 and were
> Col3(lastname) is the same within the duplicates.
>
> SELECT Col1,Col2,Col3,Col4,Col5
> FROM Table1
> WHERE Col2 IN(SELECT Col2 FROM Table1
> GROUP BY Col2
> HAVING COUNT(Col2)>1)
> ORDER BY Col2
> Sample rows
> Col1 Col2 Col3 Col4 Col5
> 532 000 doe J Intraductal EUS
> 8675 000 loe S Gastric
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
> 836 632 QQQ F Gastric
> So would like the return to be:
> Col1 Col2 Col3 Col4 Col5
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
>
> thanks
> GV
>
>
>
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
>
>

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;
What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;
|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index = idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index => idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
--
David Portas
SQL Server MVP
--

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP
--sql

duplicate rows

Hi,

I have the following records in my sql table and want to place all the duplicates in one row. I tried to do this with an update query but had no success. I can do this in access but can't figure out how to do it in sql. Any help would be great. Thanks.

qrycurrentrecords LNAME MAJOR PAPATHANASIOU 135 DEPASSQUALLO 147 BILGER 215 KLER 267 MAKO 305 PERRY 379 MILLER 379 BILLS 379 WANDER 424 FLANAGAN 440 KAUFFMAN 440 KALLIS 492 SHARKY 670

mr4100 wrote:

... want to place all the duplicates in one row.

Can you explain a litlle more about what you mean?

|||

if you take a look at the picture, you can see there are 3 codes of 379. there are other columns attached to this table i just didn't show all of them. I want to loop through the table and place the 3 rows with the same code in a row by themselves. I'm sending an email from the table and don't want to send 3 seperate emails, just one email containing data about all 3 rows. My task sends an email for each row in the table. I hope I didn't confuse you.

thanks,

|||

It is still not clear what your desired output may look like. I suggest that if would be helpful if you posted the table DDL, some sample data in the form of INSERT statements, and what the desired output looks like. Also the version of SQL Server would be helpful in finding a solution.

I'm assuming you have explored the various ways to use GROUP BY...

|||

The confusing part is when you say: duplicates. There are not duplicates here, but rather it is just a typical situation with (hopefully) a parent table and key (for major) and then the child rows with different names. The best resource for how to do this is here:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

The site is sadly an eyesore these days, but the information is still good. Use the 2005 version for sure if you are using 2005, it is really excellent and works nice.

|||

what i want to do is take these 3 seperate rows that are in the table below with the same major and put them into 1 row by themselves whether it would be updating this table or a new table by themselves,

start with this:

name major desc

john 45 eng.

mary 45 eng.

corey 25 math

rose 45 eng.

sue 15 mus.

end with this:

name major desc

john,mary,rose 45 eng.

corey 25 math

sue 15 mus.

|||

Here it is.. if you use sql server 2005

Code Snippet

Create Table #data (

[name] Varchar(100) ,

[major] Varchar(100) ,

[desc] Varchar(100)

);

Insert Into #data Values('john','45','eng.');

Insert Into #data Values('mary','45','eng.');

Insert Into #data Values('corey','25','math');

Insert Into #data Values('rose','45','eng.');

Insert Into #data Values('sue','15','mus.');

Select Distinct

Substring((Select ',' + name [text()] from #data sub where sub.major=main.major and sub.[desc]=main.[desc] For Xml Path('')),2,8000)

,major

,[desc]

from

#data main

|||

this works perfectly! Could you explain why you use the #data sub and #data main and for xml Path(")),2,8000)?

I would just like to know what it is does to make this work, I would have never figured this out.

Thanks,

Duplicate records - difference method?

Hi,
My scenario is that i have 2 system with name and adress (100.000 names),
that have to be merged into 1 system without any duplicates.
The problem is that the spelling is not 100% between the system.
One way to find duplicate is to group name,adress and count > 1.
My dream is to use the sound index "Difference" so can i get around the
spelling problem.
DIFFERENCE
Returns the difference between the SOUNDEX values of two character
expressions as an integer.
Syntax
DIFFERENCE ( character_expression , character_expression )
Is that possible to use DIFFERENCE to find duplicates?
And how should the t-sql look like?
Example
name adress city
charles way1 state1
charle waj1 stat1
charlez vay1 stat1
I want to find this example, that this 3 is duplicates.
Should i use ordinary way with group and count >1, this would not be
duplicates.
Help
Thanx
TwSo you want these to be considered duplicates:
FirstName LastName
Jon Smyth
John Smith
Jonathan Smythe
John Smyth
'|||Hi,
Yes, i want these to be considered duplicates.
But i also want to validate adress and city to see if these is duplicates.
FirstName LastName Adress City
Jon Smyth Street 1 Palace1
John Smith Stret1 Palac1
Jonathan Smythe Stret 1 Palac 1
John Smyth Street1 Palace 1
Who can i do that in a t-sql?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i
meddelandet news:uey1ZpUkFHA.2852@.TK2MSFTNGP15.phx.gbl...
> So you want these to be considered duplicates:
> FirstName LastName
> Jon Smyth
> John Smith
> Jonathan Smythe
> John Smyth
> '
>|||>> have 2 system with name and adress (100.000 names), that have to be mer
ged into 1 system without any duplicates. The problem is that the spelling i
s not 100% between the system. <<
Look up Melissa Data and get their software. Life is too short to
re-invent the wheel.|||But it cost a lot of money.
It should not be so difficult to do it in sql server.
I looking for a little help and after that i fix it, i hope :)
For the soundindex and difference function is in SQL Server.
Can i only run a t-sql and get out the difference integer, after that i fix
a algorithm.
But how should i run the t-sql to validate these values?
// tw
"--CELKO--" <jcelko212@.earthlink.net> skrev i meddelandet
news:1122318950.891968.136790@.g14g2000cwa.googlegroups.com...
> Look up Melissa Data and get their software. Life is too short to
> re-invent the wheel.
>|||>> But it cost a lot of money.<<
How much does doing it wrong cost you? How much is your time worth?|||> But how should i run the t-sql to validate these values?
T-SQL is supplying you the difference and soundex values, no?
So, you must do one of three things:
(a) determine beforehand what soundex/difference level means duplicate;
(b) inspect the results manually and make decisions; or,
(c) get software that does it right, and you might get some sleep at night.

Duplicate records

how to we check in for duplicate records without using sort (remove duplicateS)

i need to remove duplicates based on four columns.

please let me know

Are the duplicates coming from a SQL query?

The sort transformation is really the best option, in my opinion, unless you can issue a "SELECT DISTINCT" in your source pull.|||

source is a text file.Is it possible to use sort as i am deciding distinct based on four columns.

For each row coming i will be looking at four columns to decide if the records are distinct.

|||

sureshv wrote:

source is a text file.Is it possible to use sort as i am deciding distinct based on four columns.

For each row coming i will be looking at four columns to decide if the records are distinct.

Yes, pick your four columns and sort by them. Then click the remove duplicates. It will remove duplicates based on the chosen sort columns.

However, do you need to pick one row over another, or can you discard any row without knowing which row you are keeping?|||You can do this with a synchronous non-blocking script component pretty easily, especially if the data is already sorted by the rows you want to check.

The following code concatenates all your columns that you want to test for uniqueness into one string "key". If the key of the current row is different from the key of the previous row, then you redirect it to the output. Otherwise it gets dropped. This is so fast and simple that you should try to sort your data as it comes from the source in the order of these columns.

Code Snippet

Dim PreviousKey As String = ""

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim ThisKey As String = Row.FirstName + "_" + Row.LastName + "_" + Row.LastName + "_" + Row.EmailAddress
If ThisKey <> PreviousKey Then
Row.DirectRowToOutput0()
PreviousKey = ThisKey
End If
End Sub

If the data cannot be sorted, then instead of comparing ThisKey to PreviousKey, you would check for the existence of the current key in a hash table. If it doesn't exist, you redirect the row and add it. This will get slow if you have lots of unique values and you have to have enough memory to hold all that data.

|||Yet another option is to stage your data file into a sql server table and then select distinct from there.|||

JayH wrote:

You can do this with a synchronous non-blocking script component pretty easily, especially if the data is already sorted by the rows you want to check.

Nevermind. I was too slow with my post. If the source is a text file, then you should just use the sort component.
|||

If i use sort it does remove duplicates.can i stored the removed duplicates in a table .Is it possible?

|||

sureshv wrote:

If i use sort it does remove duplicates.can i stored the removed duplicates in a table .Is it possible?

The sort REMOVES duplicates. You can't redirect them. Instead, if you want to do what you've just stated, use the sort transformation but leave the remove duplicates box UNCHECKED. Then use JayH's code below to allow you to check for duplicates and then to redirect rows that are in "duplicate."|||

sureshv wrote:

If i use sort it does remove duplicates.can i stored the removed duplicates in a table .Is it possible?

Nop. That is the bad thing about the Sort transform technique. You don't have control over which rows get discarded. I always stage data in tables; then I can use sql to handle that. In SQl Server 2005 and oracle for example, I use the RANK() function to detect and enumerate the duplicates. Then in data flows I use a conditional split to let pass all myRank=1 and redirect the rest to an error table is that is required.

If you don't use staging tables; you still can acomplish it using a script component.

|||

I have one problem. I use sort component and it tries to get all data from the source. i connected my sort output to script as u said...But i dont find rows comming out of the sort output and stays yellow at sort.it dosent go to next step.What does it mean i unchecked remove duplicates..and i select 4 columns which i want and sort order for them is 1,2,3,4...is it wrong with text file or have i not set it right.

I really appreciate all help from u guys..Thank u very much...

|||

sureshv wrote:

I have one problem. I use sort component and it tries to get all data from the source. i connected my sort output to script as u said...But i dont find rows comming out of the sort output and stays yellow at sort.it dosent go to next step.What does it mean i unchecked remove duplicates..and i select 4 columns which i want and sort order for them is 1,2,3,4...is it wrong with text file or have i not set it right.

I really appreciate all help from u guys..Thank u very much...

The sort is a resource intensive transformation. It requires to 'get' all rows prior to sort/dedup them, so you won't see any row in the output untill all rows have been consumed by the sort . If the data set is big and/or memory available for SSIS is small, the package can perform poorly.

|||

the incoming data seem to have 2,190,234 rows.I know that sort will wait for all incoming rows and then sort it.So what do i need to do for its performance to increase...Is their any efficient method to run faster..

Please le me know

Thanks again

|||

sureshv wrote:

the incoming data seem to have 2,190,234 rows.I know that sort will wait for all incoming rows and then sort it.So what do i need to do for its performance to increase...Is their any efficient method to run faster..

Please le me know

Thanks again

Stage the data first in SQL server. It is another option that I gave you earlier today. Then SELECT from the source using an ORDER BY clause choosing your four columns. Then use Jay's approach to determine if the incoming record is in duplicate or not.|||

Phil Brammer wrote:

sureshv wrote:

the incoming data seem to have 2,190,234 rows.I know that sort will wait for all incoming rows and then sort it.So what do i need to do for its performance to increase...Is their any efficient method to run faster..

Please le me know

Thanks again

Stage the data first in SQL server. It is another option that I gave you earlier today. Then SELECT from the source using an ORDER BY clause choosing your four columns. Then use Jay's approach to determine if the incoming record is in duplicate or not.

If you are going to stage the data in SQL Server 2005 table; I would use the t-sql rank() function and a conditional split in the dataflow to deduplicate and redirect the non-desire rows; as I explained in my other post bellow.

duplicate records

I am having a headache working on a huge database which contains 120000 records. the big problem is that the database contains duplicates of the "serial no" of products. Another important field is the "date_purchased". How do I remove the duplicates and retain the serial no with the latest date purchased? I have managed to remove duplicates but the program does not remove on the basis of the date_purchased.Hi,

i found some info's about duplicates on planet-source-code.com as i had same problems:

see attached file (too big to quote here)|||How about doing it in 2 stages ie:-

SELECT DISTINCT SerialNo,Max(DatePurchased) From tbl GROUP BY SerialNo

Then inner Join it to

SELECT * FROM tbl

U could create 1 Statement as a Permanent View or Do it all in a stored procedure & #tmp table

Should work -

U'l prob find it can all be done in one Statement - Some1 Post & I'll C which way is faster

GW|||Or a refinement on GWilley's code

SELECT DISTINCT SerialNo, Max (DISTINCT DatePurchased)
From tbl??
Group By SerialNo

I ran this on something similar on my db (table with over 700,000 records) and it seemed to work correctly.|||you do not need DISTINCT when you use GROUP BY -- groups are distinct by definition

save the serial numbers and max dates in a working table, then delete all the rows that don't have a match in the working table

select serial, max(date_purchased) as maxdate
into keepthese
from yourtable
group by serial

delete from yourtable
where not exists
( select 1
from keepthese
where serialno = yourtable.serial
and maxdate = yourtable.date_purchased )

rudy
http://r937.com/

duplicate record

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

Im not sure how to write this query, can someone help? Thanks in advance!try something like this:

select id, fname, lastname, count(*)
from tablename
having count(*) > 1|||Use a subquery with the HAVING clause to isolate duplicated firstname/lastname records, then link to the table to get id values:

select YourTable.id,
YourTable.firstname,
YourTable.surname,
YourSubquery.occurances
from YourTable
inner join --YourSubquery
(select firstname,
surname,
count(*) as Occurances
from YourTable
group by firstname,
surname
having count(*) > 1) YourSubquery
on YourTable.firstname = YourSubquery.firstname
and YourTable.surname = YourSubquery.surname|||try something like this:

select id, fname, lastname, count(*)
from tablename
having count(*) > 1

Hmm ok, doesn't look complex at all, thanks!

It gave me error messages about not having a grouped by statement in there, so I added it. It works fine, thanks a lot!sql

Duplicate Counts per column

I'm have a problem with trying to generate a view that has a count of duplicates values per column in a table.

example I have a table with the following structure:

CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

the data loaded looks like this

INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')

the results I need are

SiteIdx 3 Digit Count 4 Digit Count
--- ---- ----
1 2 0
2 2 0

I've tried various queries the closest being:

SELECT DISTINCT SiteIdx, COUNT(*) as "3-Digit Count"
FROM dbo.TestDpln
GROUP BY SiteIdx, "3-Digit"
HAVING COUNT(*) > 1

but it only shows one column and one site I'm not sure how to get the '4 Digit Count' column to show up and the rest of the sites. below are the results I get so far.

SiteIdx 3 Digit Count
--- ----
1 2

any help would be great.
Thanks
MikeTry:
COUNT(DISTINCT [YourColumn])|||That did not work I was thinking it would need to be some sort of select statement run on each column then grouped by the siteidx then a count done by site. I'm just not sure how to write it.|||Your data and required output doesn't agree
3d 4d idx
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
No duplicates here but you want 2 0 1

INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
and no duplicates here but you want 2 0 2
And then you also ask how to display the rest of the sites
So do you want all zeros returned if a siteidx has no duplicates?|||How do you expect to get 4-Digit counts of zero from the data you supplied, which obviously contains multiple 4-Digit count values?|||Sorry for being so unclear it was a long day and not enough coffee

First the piece that I forgot to put in is that there is an input string that we pass in and are searching for such as '51212' this string is then broken down in to 3 digits from the right giving the search string for the 3 digit column of 212 and then four digits from the right giving the search string for the four digit column of 1212

so with this information and some updated data this is what I'm looking for

Input string 51212
3d 4d Site
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2212','212','2212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2213','213','2213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
3 digit 212 duplicates would be 4
4 digit 1212 duplicates would be 2

But the final output should look like

SiteIdx 3digit 4digit
--- -- --
1 4 2|||I hope this example is better|||You sure pick some bad column names.
set nocount on
CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
))

INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2212','212','2212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2213','213','2213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')

declare @.InputString char(5)
set @.InputString = '51212'

select SiteIDx,
sum(case when [3-Digit] = right(@.InputString, 3) then 1 else 0 end) as '3digit',
sum(case when [4-Digit] = right(@.InputString, 4) then 1 else 0 end) as '4digit'
from [dbo].[TestDpln]
group by SiteIDx

drop table [dbo].[TestDpln]|||That worked great a million thanks to you I never would have thought about using sum for this.
P.S.
I know the column names are bad I inherited this from someone who was trying to use MSACCESS for this data and now I need to really make it work on MSSQL we are redoing the whole schema as we build this.

Dupes

ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.
Try:
delete MyTable
where exists
(
select
*
from
MyTable m
where
m.CID = MyTable.CID
and
m.[Date] = MyTable.CID
and
m.ID > MyTable.CID
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007
IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.
|||Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:

> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>
|||On SQL Server 2005 you can do:
;WITH Dups (seq)
AS
(SELECT ROW_NUMBER() OVER(
PARTITION BY cid, date
ORDER BY id)
FROM Foo)
DELETE Dups
WHERE seq > 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Oops! God catch.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:D180E323-D5DE-4B45-A9D8-156EDC31FED4@.microsoft.com...
Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:

> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> ID CID Date
> 1 1 3/10/2008
> 2 2 3/10/2008
> 3 3 6/20/2007
> 4 3 6/20/2007
> 5 4 3/10/2008
> 6 3 3/30/2007
> 7 5 6/20/2007
> 8 3 6/20/2007
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.
>