Find targets for duplicate posts


Please login or register to vote for this query.

(click on this box to dismiss)

Each duplicate question has up to three other questions as the non-duplicate posts. This query finds the posts that duplicate questions point to. It mostly works, but no guarantees.


Q&A for community managers, administrators, and moderators

-- Bugs/missing features:
-- It parses some really old duplicates incorrectly.
-- This query doesn't account for questions that are closed as a dupe and then
-- reopened.

-- PostHistory.Text should contain up to three post IDs in square brackets,
-- separated by commas. These are the dupe targets. Now we will extract the
-- IDs in a most terrifying manner.

create table #DupeStrings (Dupe INT, Targets VARCHAR(255),
    Tgt1 VARCHAR(255), Tgt2 VARCHAR(255), Tgt3 VARCHAR(255))
insert into #DupeStrings
select top (##MaxResults?100##) PostId as [Post Link],
    -- Grab text from after the first '[' to the first ']'
        charindex('[', Text) + 1,
        charindex(']', Text) - charindex('[', Text) - 1
    ) + ',,,',
    0, 0, 0
from PostHistory h
where PostHistoryTypeId = 10 -- post closed
and Comment in (1, 101) -- as a duplicate
order by CreationDate

-- this part is awful. Take three passes to extract the post IDs
update #DupeStrings
set tgt1 = left(Targets, charindex(',', Targets) - 1),
    tgt2 = substring(Targets, charindex(',', Targets) + 1, 100),
    tgt3 = substring(Targets, charindex(',', Targets) + 1, 100)

update #DupeStrings
set tgt2 = left(tgt2, charindex(',', tgt2) - 1),
    tgt3 = substring(tgt3, charindex(',', tgt3) + 1, 100)

update #DupeStrings
set tgt3 = left(tgt3, charindex(',', tgt3) - 1)

-- Now let's create a nice and neat table of dupe target IDs
-- and forget we ever saw that code above.
select Dupe,
    nullif(cast(tgt1 as int),0) as Tgt1,
    nullif(cast(tgt2 as int),0) as Tgt2,
    nullif(cast(tgt3 as int),0) as Tgt3
into #Dupes
from #DupeStrings

-- Now let's list what we found
    'site://questions/' + cast(Dupe as VARCHAR) + '|' + d.Title as Dupe,
    'site://questions/' + cast(Tgt1 as VARCHAR) + '|' + t1.Title as Tgt1,
    'site://questions/' + cast(Tgt2 as VARCHAR) + '|' + t2.Title as Tgt2,
    'site://questions/' + cast(Tgt3 as VARCHAR) + '|' + t3.Title as Tgt3
from #Dupes
left join Posts d on Dupe = d.Id
left join Posts t1 on Tgt1 = t1.Id
left join Posts t2 on Tgt2 = t2.Id
left join Posts t3 on Tgt3 = t3.Id
where t1.ClosedDate is not null
   or t2.ClosedDate is not null
   or t3.ClosedDate is not null
order by d.CreationDate desc

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results