SQL Indent Help - SQL Server Forums

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)

SQL Indent Help

Author

Topic

shox
Starting Member

3 Posts

Posted - 2009-11-13 : 07:24:10

Hi All,

I've got a quick question, and would be extremely greatful if someone can help.

Im trying to identify dupes within a table.

I've done this so far to determine the dupes:

select surname,address_line1,count(*)
from TestTable
group by surname,address_line1 having count(*) > 1
order by surname

but what i need to do now is to display all the information for these records (that are duped), which includes the unique ID. I'm trying to do it without creating another table, but am finding it difficult.

i've tried top use an indent sql statement, but got stuck.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 07:27:28

select t1.*
from TestTable t1
join
(select surname,address_line1,count(*)
from TestTable
group by surname,address_line1 having count(*) > 1)dt
on dt.surname=t1.surname and dt.address_line1=t1.address_line1
order by t1.surname

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources