I need to see duplicates with their recid.

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

SQL Server 2000 Forums

Transact-SQL (2000)

I need to see duplicates with their recid.

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2006-01-26 : 11:14:12

if I do this query it shows me the records with duplicate names:


select firstname,lastname,statusid from employees 
group by firstname,lastname,statusid
having count(*) > 1

I would like to see all the ones that are duplicate with their record id, example:

recid....firstname....statusid
------------------------------
1..........Joe..........TERM
45........Joe..........EMPLOYEE

Is there a way to do this ?

robert

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-26 : 11:43:53

Try
Select e.* from employees e
Inner Join
(Select firstname, statusid from employees group by firstname, statusid having count(*) > 1) Qry1
on e.firstname = Qry1.firstname and e.statusid = Qry1.statusid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-26 : 20:07:41

[code]select e.recid, e.firstname, e.statusid
from employees e
inner join
(
-- this is your duplicate names query
select firstname, lastname, statusid
from employees
group by firstname, lastname, statusid
having count(*) > 1
) as d
on e.firstname = d.firstname
and e.lastname = d.lastname
and e.statusid = d.statusid[/code]

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources