DupeTree: Find all duplicates of a question

1

Please login or register to vote for this query.

(click on this box to dismiss)

This query finds all direct and indirect duplicates of a question on a given site. (Cross-site duplicates, as might arise e.g. due to migration, are unfortunately too expensive to search for.) To use it, enter the numerical question ID of the target question (i.e. the question which the others have been closed as duplicates of) as PostId below. The results always list the target question first, followed by its duplicates; if the duplicates themselves have duplicates, those will be listed after their parent (i.e. the question they have been closed as a duplicate of). Each question will only be listed once, even if it has multiple parents in the tree.

Stack Overflow

Q&A for professional and enthusiast programmers

Create Table #DupeTree (
  DupeId int not null primary key,
  Path nvarchar(max)
)

Select * Into #LastLevel From #DupeTree
Insert Into #LastLevel Values ( ##PostId:int##, Cast(##PostId:int## as nvarchar) )

While (Select count(*) From #LastLevel) > 0
Begin
  Insert Into #DupeTree Select * From #LastLevel

  Select
    PostId as DupeId,
    Min(Path + ', ' + Cast(PostId as nvarchar)) as Path
  Into #NextLevel
  From PostLinks Join #LastLevel on DupeId = RelatedPostId
  Where LinkTypeId = 3
    And PostId not in (Select DupeId from #DupeTree)
  Group by PostId

  Delete From #LastLevel
  Insert Into #LastLevel Select * From #NextLevel
  Drop Table #NextLevel
End;

With DupeTreeWithAnswerScore as (
  Select DupeId, Path, Max(Score) as [AnswerScore]
  From #DupeTree Left Join Posts on ParentId = DupeId
  Group By DupeId, Path
)
Select
  Question.Id as [Post Link],
  Path,
  Case
    When Author.Id is null Then Question.OwnerDisplayName
    Else 'site://users/' + Cast(Author.Id as nvarchar) + '|' + Author.DisplayName
  End as [Author],
  Question.Score,
  Question.AnswerCount as [Answers],
  AnswerScore as [AScore],
  Question.CreationDate,
  Question.ViewCount as [Views],
  Question.Tags as [Tags]
From
  DupeTreeWithAnswerScore
  Join Posts as Question on Question.Id = DupeId
  Left Join Users as Author on Author.Id = Question.OwnerUserId
Order by Path

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results