Finding the same data in 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 2000 Forums

Transact-SQL (2000)

Finding the same data in table

Author

Topic

David Lopez
Starting Member

19 Posts

Posted - 2005-07-05 : 12:55:02

Hi,

How can I find the record which have the same number?

Select *
Where column1=column1
From TableX

Or something like that

I played around with the commands Unique, but no luck.

Thx already for any respons

David

KLang23
Posting Yak Master

115 Posts

Posted - 2005-07-05 : 13:31:49

Hi,


Select Col1, Count(*) as Number_of_Duplicates
From TableX
Group By Col1 Having Count(*) > 1

sfrigard
Starting Member

5 Posts

Posted - 2005-07-05 : 13:38:35

If you mean finding records containing the same value then the following query will show records that have the same value.

Select Column1
from TableX
group by Column1
having count(*) > 1

If you want to see the entire record each time it appears. Then the following query will work.

Select t1.*
from TableX as t1
inner Join TableX as t2
on t1.Column1 = t2.Column1

This could produce a lot of redundant coulumns if the record appears more that two times.

The following query will address that problem but may be slower.

Select *
from TableX
where Column1 in (Select Column1
from TableX
group by Column1
having count(*) > 1)

sfrigard
Starting Member

5 Posts

Posted - 2005-07-05 : 13:39:26

quote:
Originally posted by sfrigard

If you mean finding records containing the same value then the following query will show records that have the same value.

Select Column1
from TableX
group by Column1
having count(*) > 1

If you want to see the entire record each time it appears. Then the following query will work.

Select t1.*
from TableX as t1
inner Join TableX as t2
on t1.Column1 = t2.Column1

This could produce a lot of redundant records if the record appears more that two times.

The following query will address that problem but may be slower.

Select *
from TableX
where Column1 in (Select Column1
from TableX
group by Column1
having count(*) > 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources