Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
/* Select the top 100 original posts and how many duplicates they have */ /* from all duplicate questions. */ SELECT TOP 100 CAST(QUESTION.Identifier AS INT) AS [Post Link], COUNT(QUESTION.PostId) AS DuplicateCount FROM ( /* Select the Identifier part out of the path */ /* from all duplicate link URLs. */ SELECT PostId, SUBSTRING(URL.Path, 0, CHARINDEX('/', URL.Path)) Identifier FROM ( /* Select the path part from the URL after '(questio)ns/' */ /* from the duplicate notices in the post history of the community user. */ SELECT PostId, SUBSTRING(Text, CHARINDEX('ns/', Text) + LEN('ns/'), 999) Path FROM PostHistory WHERE UserId = -1 AND Comment = 'insert duplicate link' ) AS URL ) AS QUESTION GROUP BY QUESTION.Identifier ORDER BY DuplicateCount DESC