April 22, 2003 at 12:32 pm
Hi,
I'm kind of new to T/SQL and was simply wondering if someone could provide me with a query that will list all the duplicates (first name, last name, and company name) in a table. Your response is greatly appreciated.
Thanks,
Jonas
April 22, 2003 at 1:46 pm
I'd do an outer join on the table itself.
Example:
select a.* from Customer a
inner join Customer b
where a.Id <> b.Id --Use whatever keys are in this table
and a.firstnm = b.firstnm
and a.lastnm = b.lastnm
and a.compnm = b.compnm
Darren
Darren
April 22, 2003 at 1:54 pm
if you also want to know how many times they occur you can use a group by query:
SELECT COUNT(City) AS howmany, City
FROM Customers
GROUP BY City
HAVING (COUNT(City) > 2)
April 22, 2003 at 2:09 pm
I think you'll find the following is faster than the self join method:
SELECT FirstName, LastName, Company, <PK> FROM tblname
WHERE FirstName In (SELECT FirstName FROM tblname As Tmp GROUP BY FirstName,LastName,Company
HAVING Count(*)>1 And Tmp.LastName = tblname.LastName And Tmp.Company = tblname.Company)
ORDER BY FirstName, LastName, Company
Also, you would need to add distinct to the self join method to eliminate extra duplicates for situations where there is more than one duplicate.
Jay Madren
Jay Madren
April 22, 2003 at 2:18 pm
CORRECTION: criteria should be >1 not >2
SELECT COUNT(City) AS howmany, City
FROM Customers
GROUP BY City
HAVING (COUNT(City) > 1)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply