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.
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 TABLEMRS-SMITH-VIRGINA-EMRS-SMITH-VIRGINA-EMRS-SMITH-VIRGINA-RMR-EVANS-TOM-EMR-EVANS-TOM-EMR-DOE-JOHN-MS-JOHNSON-BETH-XMS-JOHNSON-BETH-YMR-JOHNSON-BETH-YDESIRED QUERY RESULTSMRS-SMITH-VIRGINA-EMRS-SMITH-VIRGINA-RMS-JOHNSON-BETH-XMS-JOHNSON-BETH-Y |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-24 : 11:55:03
|
SELECT DISTINCT t1.*FROM @TABLE t1INNER JOIN(select nameprefix, lastname, firstnamefrom yourTablegroup by nameprefix, lastname, firstnamehaving COUNT(distinct middleinit) > 1) t2 ON t1.firstname = t2.firstname and t1.lastname = t2.lastname and t1.nameprefix = t2.nameprefixJimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 01:26:19
|
[code]SELECT nameprefix, lastname, firstnameFROM(SELECT *,COUNT(1) OVER (PARTITION BY nameprefix, lastname, firstname) AS Cnt,ROWNUMBER() OVER (PARTITION BY nameprefix, lastname, firstname ORDER BY nameprefix ASC) AS SeqFROM table)tWHERE Seq=1AND Cnt>1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|