CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Furkan Gözükara
Furkan Gözükara

Reputation: 23830

Find 1 column same other column different rows of the same table

Is the query below correct to find columns for which the HashCode is the same but PageUrl different rows? This would mean basically collisions in my database planning.

select T1.PageUrl,T1.HashCode from tblPages as T1 inner join
(select PageUrl,HashCode from tblPages group by PageUrl,HashCode) as T2 on
T1.PageUrl!=T2.PageUrl and T1.HashCode=T2.HashCode

I am using 64 bit HashCode.

MS-SQL 2008 R2

Upvotes: 0

Views: 719

Answers (2)

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

I'd use a CROSS JOIN with a WHERE clause rather than your INNER JOIN. 

SELECT T1.PageUrl,T1.HashCode FROM tblPages AS T1
CROSS JOIN (SELECT DISTINCT PageUrl,HashCode FROM tblPages) AS T2
WHERE T1.PageUrl <> T2.PageUrl AND T1.HashCode = T2.HashCode

The reason for this is that the optimizer will give you a better query plan with the cross join. Link to an example.

Upvotes: 1

Sparky
Sparky

Reputation: 15075

Pretty close, i would go with this:

select T1.PageUrl,T1.HashCode from tblPages as T1 
inner join (select distinct PageUrl,HashCode from tblPages) as T2 
on T1.PageUrl <> T2.PageUrl and T1.HashCode=T2.HashCode

Upvotes: 1

Related Questions