How to check for duplicate records ina table

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 2005 Forums

Transact-SQL (2005)

How to check for duplicate records ina table

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-20 : 16:04:52

Is it possible to check for duplicate records in a table based on 2 fields info.

Select * from Table_Orders where ord_code and ord_comp > 1

Thank you very much for the helpful information.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 16:06:41

yes.

select field1,field2,count(*)
from
yourtable
where
... yourfilter
group by 
field1,field2
having count(*)>1

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-20 : 16:31:22

Hello Saket, i am not passing anything in the filter.

If it is just field i use this:

select * from TAB_UserRoles
where UserID in (select UserID from TAB_UserRoles group by UserID having count(*) > 1 )

With two fields i am confused how to place them.

quote:

Originally posted by sakets_2000

yes.

select field1,field2,count(*)
from
yourtable
where
... yourfilter
group by 
field1,field2
having count(*)>1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 16:54:54

You have to find duplicates basis which 2 fields ? Can you post the names ?

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-03-20 : 17:01:19

Select * from Table_Orders
Fieldnames: ord_code & ord_comp.

Thanks a lot for the help.

quote:
Originally posted by sakets_2000

You have to find duplicates basis which 2 fields ? Can you post the names ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 17:06:39

this helps ?


select 
	ord_code , 
	ord_comp, 
	count(*) 
from 
	TAB_UserRoles 
group by 
	ord_code , ord_comp 
having 	
	count(*) > 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-22 : 00:36:40

select * from
( select *,row_number() over( partition by ord_code,ord_comp order by ord_code,ord_comp ) as rn from table ) t
where t.rn > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources