SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Query Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thebrenda
Starting Member

7 Posts
Posted - 01/24/2013 :  11:34:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 01/24/2013 :  11:55:03  Show Profile  Reply with Quote

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

India
47173 Posts
Posted - 01/25/2013 :  01:26:19  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000