Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Query Help

Author  Topic 

thebrenda
Starting Member

22 Posts

Posted - 2013-01-24 : 11:34:00
I need help with a query.

SQL Table has fields: nameprefix, lastname, firstname, middleinit. Want to identify all the rows where nameprefix, lastname, firstname match rows that have the same values for those 4 fields but a different value for middle name. Want to find suspect duplicate names.

SQL TABLE
MRS-SMITH-VIRGINA-E
MRS-SMITH-VIRGINA-E
MRS-SMITH-VIRGINA-R
MR-EVANS-TOM-E
MR-EVANS-TOM-E
MR-DOE-JOHN-
MS-JOHNSON-BETH-X
MS-JOHNSON-BETH-Y
MR-JOHNSON-BETH-Y

DESIRED QUERY RESULTS
MRS-SMITH-VIRGINA-E
MRS-SMITH-VIRGINA-R
MS-JOHNSON-BETH-X
MS-JOHNSON-BETH-Y

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-24 : 11:55:03

SELECT DISTINCT t1.*
FROM @TABLE t1
INNER JOIN
(

select nameprefix, lastname, firstname
from yourTable
group by nameprefix, lastname, firstname
having COUNT(distinct middleinit) > 1
) t2 ON t1.firstname = t2.firstname
and t1.lastname = t2.lastname and t1.nameprefix = t2.nameprefix

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 01:26:19
[code]
SELECT nameprefix, lastname, firstname
FROM
(
SELECT *,COUNT(1) OVER (PARTITION BY nameprefix, lastname, firstname) AS Cnt,
ROWNUMBER() OVER (PARTITION BY nameprefix, lastname, firstname ORDER BY nameprefix ASC) AS Seq
FROM table
)t
WHERE Seq=1
AND Cnt>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -