How to Eliminate Duplicate Records on A Query?

[aalmeida]

I want to Select * from ##TN008LMSH11 that do not exists ON Customer,
but IF there IS more than one record I want only the LAST ##TN008LMSH11.ChangedateTime.

The following query is bringin me all duplicate recors as well.

SELECT t.CustID, t.CampaignID, t.Office,
t.LastName, t.FirstName, t.MiddleInitial, t.AddressLine1,
t.AddressLine2, t.SSN, t.City, t.State, t.HomePhone,
t.Zip, t.ResidCD, t.ResidDate, t.EmployerName, t.JobTitle,
t.EmploymentDate, t.WorkPhone, t.OtherPhone, t.BTTC,
t.BTTCCode, t.EmailAddress, t.CustType, t.ChangeDateTime
FROM ##TN008LMSH11 t LEFT JOIN Customer c
ON t.CustID=c.CustID AND
t.CampaignID=c.CampaignID AND
t.Office=c.Office
WHERE c.CustID IS NULL

do you have a suggestion on how to do it? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[BeckahC]

Not sure of the syntax, I've only needed it a couple of times in the past, but I think there is a "select unique"
that would work... I'll have to check BOL to see if I can find it again... BeckahC

 

[jhall156]

select distinct

But if you want to have the last field value(in natural order) of the duplicate records you may want to GROUP your records by your custid and then use aggregate functions like MIN and MAX to get your distinct values. If you need to filter the results on an aggregated value place the criteria in a HAVING clause. JHall

 

[BeckahC]

ahh... as I said...it's been awhile. Distinct sounds much more familiar!;-)

BeckahC

 

[aalmeida]

BeckahC
Distinct would not give me necessarily the record with Last Date but the first one to be found by the search, therefore would not guarantee the correct answer

JHall the group by + agregate MAX did you mean like:

SELECT t.CustID, t.CampaignID, t.Office,
t.LastName, t.FirstName, t.MiddleInitial, t.AddressLine1,
t.AddressLine2, t.SSN, t.City, t.State, t.HomePhone,
t.Zip, t.ResidCD, t.ResidDate, t.EmployerName, t.JobTitle,
t.EmploymentDate, t.WorkPhone, t.OtherPhone, t.BTTC,
t.BTTCCode, t.EmailAddress, t.CustType, MAX(t.ChangeDateTime)
FROM ##TN008LMSH11 t LEFT JOIN Customer c
ON t.CustID=c.CustID AND
t.CampaignID=c.CampaignID AND
t.Office=c.Office
WHERE c.CustID IS NULL
GROUP BY t.CustID, t.CampaignID, t.Office,
t.LastName, t.FirstName, t.MiddleInitial, t.AddressLine1,
t.AddressLine2, t.SSN, t.City, t.State, t.HomePhone,
t.Zip, t.ResidCD, t.ResidDate, t.EmployerName, t.JobTitle,
t.EmploymentDate, t.WorkPhone, t.OtherPhone, t.BTTC,
t.BTTCCode, t.EmailAddress, t.CustType, t.ChangeDateTime

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[BeckahC]

If select your records into a temp table with an order by statement (decending order by date), then select distinct from the temp table, it might work...?

Hope this helps... BeckahC

 

[jhall156]

Yep. You may find that you need even more domain aggregation to relieve your result set of duplicates. JHall

 

[aalmeida]

Yes I might because I'm still getting
Violation of PRIMARY KEY constraint 'PK_Customer'. Cannot insert duplicate key in object 'Customer'.
The result from the Select Statment stil has something that already exists on Customer Table and that is my hole porpose here is to be able to filter out the dups so I can insert AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[jhall156]

I'm not quite following you. Just as a side note,
I don't think you want to group by t.ChangeDateTime because that is the field of which you're returning max. Is the constraint on your main customer table and you're trying to insert into? It doesn't seem like that can be because your where clause specifies a null in that table. I'm trying to get my head wrapped around it but I'm a bit burned out today. What is the pk? JHall

 

[jhall156]

Oh wait, null is a reference to unknown not empty. I guess it's because I've never seen that. I use NOT EXISTS in the where clause for that kind of a deal. JHall

 

[aalmeida]

The PK is: CustID, CampaignID, Office, I put the is null clause so the left inner join would not give me records from the main Customer table only from my temp table.
can you give an example of "not exists"? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[jhall156]

SELECT a.* FROM tablename1 a
WHERE NOT EXISTS(
SELECT b.field FROM tablename2 b
WHERE b.field = a.field)


i.e. "Give me all the records from tablename1 that do not have a corresponding record in tablename2"
JHall

 

[tlbroadbent]

Al,

Try the following. Adjust the criteria in the sub-query as needed to select the appropriate MAX date.
[tt]
SELECT t.CustID, t.CampaignID, t.Office,
t.LastName, t.FirstName, t.MiddleInitial, t.AddressLine1,
t.AddressLine2, t.SSN, t.City, t.State, t.HomePhone,
t.Zip, t.ResidCD, t.ResidDate, t.EmployerName, t.JobTitle,
t.EmploymentDate, t.WorkPhone, t.OtherPhone, t.BTTC,
t.BTTCCode, t.EmailAddress, t.CustType, t.ChangeDateTime

FROM ##TN008LMSH11 t
LEFT JOIN Customer c
ON t.CustID=c.CustID AND
t.CampaignID=c.CampaignID AND
t.Office=c.Office
WHERE c.CustID IS NULL
AND t.ChangeDateTime = (Select Max(ChangeDateTime) FROM ##TN008LMSH11 WHERE CustID=t.CustID)[/tt]
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm