Re: how to find duplicate data involving more than one field

microsoft.public.sqlserver.programming

16 Dec 2005 6:16 PM

rlassiter

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

David Portas wrote:
Show quote

> *SELECT firstname, lastname
> FROM YourTable
> GROUP BY firstname, lastname
> HAVING COUNT(*)>1
>
> --
> David Portas
> SQL Server MVP
> -- * -- rlassiter ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------

18 Dec 2005 11:59 PM

Hugo Kornelis

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)