Seek for duplicate rows - 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 2000 Forums

SQL Server Development (2000)

Seek for duplicate rows

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-02-10 : 17:30:08

Hi,
I read some book that says the following query will seek for duplicate rows in a table. However, the book does not gives me a clear explanation
1/ why the query can do that?
2/ why do we have to use JOIN not INNER JOIN?
Any help would be great appreciated.

--- a row that is in table "compliancehousehold" and is duplicated if
-- its values is specified in the join.

select C1.*
from compliancehousehold C1
join compliancehousehold C2
on C1.reshID = C2.reshID
and C1.reshstartDate = C2.reshstartDate
and C1.ReshHudNextAnnualDate is null
and C1.chid < C2.chid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-10 : 17:51:50

[code]
select C1.*
from compliancehousehold C1
join (
select reshID, reshstartDate
from compliancehousehold
group by reshID, reshstartDate
having count(*) > 1
) C2
on C1.reshID = C2.reshID
and C1.reshstartDate = C2.reshstartDate
and C1.ReshHudNextAnnualDate is null
[/code]

Kristen
Test

22859 Posts

Posted - 2007-02-11 : 05:26:51

"why the query can do that?"

The table is joined to itself. It will match every record where reshID and reshstartDate match (and ReshHudNextAnnualDate is null in the first instance of the table - obviously something specific about that test)

By joining the table to itself the record will match itself, but also any additional duplicate values.

and

C1.chid < C2.chid

then ensures that

C1.chid = C2.chid

is NOT matched. Obviously the record will match itself! and also the test is specifically NOT:

C1.chid <> C2.chid

which would match a single duplicate twice (once where C1.chid < C2.chid and again later on where C1.chid > C2.chid - i.e. the same duplicate record pair matched the other way round)

I expect this is a lot more "work" than is necessary, and it would be better to start with the HAVING COUNT(*) > 1 test that khtan suggested

"why do we have to use JOIN not INNER JOIN?"

JOIN and INNER JOIN are the same thing.

Kristen

johnsql
Posting Yak Master

161 Posts

Posted - 2007-02-11 : 20:38:54

Thanks so much Kriten and Khtan. Your words help me better understand the issue. And I will try your other suggested way like GROUP BY and HAVING. It is very great for me know such another way to find out duplicate rows in the table.
Johnsql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources