The longest dupe chains of the site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

CREATE TABLE #Dupes (
  DupeId INT,
  OrigId INT,
  Depth INT
);

INSERT INTO #Dupes
SELECT
  PostLinks.PostId AS DupeId,
  PostLinks.RelatedPostId AS OrigId, 1 AS Depth
FROM PostLinks, Posts D, Posts O
WHERE
  PostLinks.PostId = D.Id
  AND
  PostLinks.RelatedPostId = O.Id
  AND
  PostLinks.LinkTypeId = 3
  AND
  PostLinks.PostId != PostLinks.RelatedPostId;

DECLARE @Depth INT = 1;

WHILE @Depth < 5
BEGIN
  INSERT INTO #Dupes
  SELECT
    Cur.DupeId AS DupeId,
    Nxt.OrigId AS OrigId,
    @Depth + 1
  FROM #Dupes Cur, #Dupes Nxt
  WHERE
    Cur.Depth = @Depth
    AND
    Cur.OrigId = Nxt.DupeId
    AND
    Nxt.Depth = 1;
  SET @Depth = @Depth + 1;
END;

SELECT TOP 100
  DupeId AS [Post Link],
  *
FROM #Dupes
ORDER BY Depth DESC, DupeId, OrigId

Enter Parameters

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