List of duplicate questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stellar Meta

Q&A about the site for developers and users of Stellar and the Stellar Distributed Exchange

-- TagName: Filter questions by this tag. Leave empty (' ') to list all questions.

-- Some deleted posts still live in PostLinks.RelatedPostId because they are the
-- closure target of a non-deleted post. I've excluded those from the query below.

-- When was the new duplicate-closure system (with real banners rather than ad
-- hoc markdown) implemented? <http://meta.stackexchange.com/q/166707/224428>
DECLARE @lti3_start_date AS datetime = (
  SELECT MIN(CreationDate) FROM StackOverflow..PostLinks WHERE LinkTypeId = 3);

WITH raw AS ( -- grab both real duplicate banners and pseudo duplicate banners
  SELECT
    PostId,
    RelatedPostId
  FROM
    PostLinks AS pl
  WHERE
    pl.LinkTypeId = 3 -- duplicate
  
  UNION
  
  SELECT
    PostId,
    RelatedPostId
  FROM
    PostLinks AS pl
  WHERE
    pl.LinkTypeId = 1
    AND pl.CreationDate < @lti3_start_date -- safety check
    AND EXISTS (
      SELECT 1 
      FROM Posts AS p 
      WHERE 
        p.Id = pl.PostId 
        AND p.Body LIKE 
'%<blockquote>
  <p><strong>Possible Duplicate:</strong><br>
  <a href="%/questions/' + CAST(pl.RelatedPostId AS nvarchar) + '/%</a>  </p>
</blockquote>%'
    )
)

SELECT DISTINCT TOP 100000
  r1.RelatedPostId,
  p.Id as OId,
  p.Title as OTitle,
  p.Tags as OTags,
  q.Id as DId,
  q.Title as DTitle,
  q.Tags as DTags
  
FROM  
  raw AS r1
  INNER JOIN Posts AS p ON r1.RelatedPostId = p.Id
  INNER JOIN Posts AS q ON q.Id = r1.PostId
WHERE
  ##TagName:string? ## = ' '
  OR ##TagName## IN (
    SELECT t.TagName
    FROM Tags AS t
    INNER JOIN PostTags AS pt ON pt.TagId = t.Id
    WHERE pt.PostId = p.Id
  )
   ORDER BY r1.RelatedPostId ASC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results