how to find duplicate data involving more than one field

How can I query a database that checks for duplicate data in a combination
of fields. For instance, LastName may have many duplicates but I want to
find duplicates of LastName combined with FirstName. Thanks.SELECT firstname, lastname
FROM YourTable
GROUP BY firstname, lastname
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||This is great to show what is duplicated and by adding changing Select to co
unt(*) I was able to see how many times it was duplicated. Are you able to t
ake this 1 step further and actually return all duplicated and complete reco
rds? EG. John,Smith is duplicated 3 times but the City is different in each
case. Can you return the 3 first,last,city records?
Robert Lassiter

quote:

---

Originally posted by David Portas
SELECT firstname, lastname
FROM YourTable
GROUP BY firstname, lastname
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--

---

|||On Fri, 16 Dec 2005 12:16:57 -0600, rlassiter wrote:

>This is great to show what is duplicated and by adding changing Select
>to count(*) I was able to see how many times it was duplicated. Are you
>able to take this 1 step further and actually return all duplicated and
>complete records? EG. John,Smith is duplicated 3 times but the City is
>different in each case. Can you return the 3 first,last,city records?
>Robert Lassiter
Hi Robert,
Here's one method:
SELECT a.firstname, a.lastname, a.city
FROM YourTable AS a
WHERE (SELECT COUNT(*)
FROM YourTable AS b
WHERE b.firstname = a.firstname
AND b.lastname = a.lastname) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)