Tasty and delicious Dupe Loops

1

Please login or register to vote for this query.

(click on this box to dismiss)

Finds loops of questions closed as duplicates of each other. Returns the oldest question (by question ID) in each loop. This query currently finds loops of up to 20 questions, but is easily extensible.

Stack Overflow

Q&A for programmers

With Dupes as (
  Select PostId as Source, RelatedPostId as Target
  From PostLinks
  Where LinkTypeId = 3
)
Select Distinct
  Case
    When Dupe1.Target = Question.Id Then 1
    When Dupe2.Target = Question.Id Then 2
    When Dupe3.Target = Question.Id Then 3
    When Dupe4.Target = Question.Id Then 4
    When Dupe5.Target = Question.Id Then 5
    When Dupe6.Target = Question.Id Then 6
    When Dupe7.Target = Question.Id Then 7
    When Dupe8.Target = Question.Id Then 8
    When Dupe9.Target = Question.Id Then 9
    When Dupe10.Target = Question.Id Then 10
    When Dupe11.Target = Question.Id Then 11
    When Dupe12.Target = Question.Id Then 12
    When Dupe13.Target = Question.Id Then 13
    When Dupe14.Target = Question.Id Then 14
    When Dupe15.Target = Question.Id Then 15
    When Dupe16.Target = Question.Id Then 16
    When Dupe17.Target = Question.Id Then 17
    When Dupe18.Target = Question.Id Then 18
    When Dupe19.Target = Question.Id Then 19
    When Dupe20.Target = Question.Id Then 20
  End as [Len],
  Question.Id as [Post Link],
  Case
    When Author.Id is null Then Question.OwnerDisplayName
    Else 'site://users/' + Cast(Author.Id as Varchar) + '|' + Author.DisplayName
  End as [Author],
  Question.Score,
  Question.CreationDate,
  Question.ViewCount as [Views],
  Question.Tags as [Tags]
From
  Posts as Question
  Join Dupes as Dupe1 on Dupe1.Source = Question.Id
  Join Dupes as Dupe2 on Dupe2.Source = Dupe1.Target
  Join Dupes as Dupe3 on Dupe3.Source = Dupe2.Target
  Join Dupes as Dupe4 on Dupe4.Source = Dupe3.Target
  Join Dupes as Dupe5 on Dupe5.Source = Dupe4.Target
  Join Dupes as Dupe6 on Dupe6.Source = Dupe5.Target
  Join Dupes as Dupe7 on Dupe7.Source = Dupe6.Target
  Join Dupes as Dupe8 on Dupe8.Source = Dupe7.Target
  Join Dupes as Dupe9 on Dupe9.Source = Dupe8.Target
  Join Dupes as Dupe10 on Dupe10.Source = Dupe9.Target
  Join Dupes as Dupe11 on Dupe11.Source = Dupe10.Target
  Join Dupes as Dupe12 on Dupe12.Source = Dupe11.Target
  Join Dupes as Dupe13 on Dupe13.Source = Dupe12.Target
  Join Dupes as Dupe14 on Dupe14.Source = Dupe13.Target
  Join Dupes as Dupe15 on Dupe15.Source = Dupe14.Target
  Join Dupes as Dupe16 on Dupe16.Source = Dupe15.Target
  Join Dupes as Dupe17 on Dupe17.Source = Dupe16.Target
  Join Dupes as Dupe18 on Dupe18.Source = Dupe17.Target
  Join Dupes as Dupe19 on Dupe19.Source = Dupe18.Target
  Join Dupes as Dupe20 on Dupe20.Source = Dupe19.Target
  Left Join Users as Author on Author.Id = Question.OwnerUserId
Where
  Question.PostTypeId = 1
  And Question.Id in (
    Dupe1.Target, Dupe2.Target, Dupe3.Target,
    Dupe4.Target, Dupe5.Target, Dupe6.Target,
    Dupe7.Target, Dupe8.Target, Dupe9.Target,
    Dupe10.Target, Dupe11.Target, Dupe12.Target,
    Dupe13.Target, Dupe14.Target, Dupe15.Target,
    Dupe16.Target, Dupe17.Target, Dupe18.Target,
    Dupe19.Target, Dupe20.Target)
  And Question.Id <= Dupe1.Target
  And Question.Id <= Dupe2.Target
  And Question.Id <= Dupe3.Target
  And Question.Id <= Dupe4.Target
  And Question.Id <= Dupe5.Target
  And Question.Id <= Dupe6.Target
  And Question.Id <= Dupe7.Target
  And Question.Id <= Dupe8.Target
  And Question.Id <= Dupe9.Target
  And Question.Id <= Dupe10.Target
  And Question.Id <= Dupe11.Target
  And Question.Id <= Dupe12.Target
  And Question.Id <= Dupe13.Target
  And Question.Id <= Dupe14.Target
  And Question.Id <= Dupe15.Target
  And Question.Id <= Dupe16.Target
  And Question.Id <= Dupe17.Target
  And Question.Id <= Dupe18.Target
  And Question.Id <= Dupe19.Target
  And Question.Id <= Dupe20.Target

Enter Parameters

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