Retrieve all questions that are marked as duplicated, with additional information.


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

  questions.Id AS QuestionId,
  questions.Title AS QuestionTitle,
  questions.Body AS QuestionBody,
  questions.Score AS QuestionScore,
  questions.AcceptedAnswerId AS AcceptedAnswer,
  accepted.Score AS AcceptedAnswerScore,
  answers.Id AS MostVotedAnswer,
  answers.Score AS MostVotedAnswerScore,
  -- History text example: {"OriginalQuestionIds":[1234],"Voters":[{"Id":5678,"DisplayName":"Foo Bar"}]}
  SUBSTRING(history.Text, CHARINDEX('[', history.Text) + 1, CHARINDEX(']', history.Text) - CHARINDEX('[', history.Text) - 1) AS DuplicateQuestions,
  LEN(history.Text) - LEN(REPLACE(history.Text, '"Id"', '###')) AS DuplicateQuestionsVotes
  Posts questions
  LEFT JOIN Posts accepted ON accepted.Id = questions.AcceptedAnswerId
  LEFT JOIN Posts answers ON answers.ParentId = questions.Id
  LEFT JOIN PostHistory history ON history.PostId = questions.Id
  ( -- It must me marked as duplicated
    history.Id IN (
      SELECT TOP 1
      FROM PostHistory h
      WHERE (
        ( -- Is history of the correct question
          h.PostId = questions.Id
        ) AND ( -- Is Closed
          h.PostHistoryTypeId = 10
        ) AND ( -- Is duplicated
          h.Text LIKE '{"OriginalQuestionIds":%'
      ORDER BY h.Id DESC
  ) AND ( -- The answer retrieved must be the most voted answer
      answers.Id IS NULL
    ) OR (
      answers.Id IN (
        SELECT TOP 1
        FROM Posts p
        WHERE ( -- Is answer of the correct question
          p.ParentId = questions.Id
        ORDER BY p.Score DESC, p.Id DESC

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results