Finding duplicate entries (with different keys)

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

Finding duplicate entries (with different keys)

Author

Topic

davidmal
Starting Member

19 Posts

Posted - 2007-02-08 : 22:28:31

Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name.

How could you query this:

ID fname lname
10001 Bill Jones
10002 Joe Smith
10003 Sue Jenkins
10004 John Sanders
10005 Joe Smith
10006 Harrold Simpson
10007 Sue Jenkins
10008 Sam Worden

and get a result set of this:

ID fname lname
10002 Joe Smith
10005 Joe Smith
10003 Sue Jenkins
10007 Sue Jenkins

intergalacticplanetary
Starting Member

12 Posts

Posted - 2007-02-08 : 23:30:03

SELECT ID, fname, lname
FROM table
ORDER BY fname, lname;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 23:32:17

[code]
select t.*
from table1 t
inner join
(
select fname, lname
from table1
group by fname, lname
having count(*) > 1
) d
on t.fname = d.fname
and t.lname = d.lname
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources