0

Please login or register to vote for this query.

(click on this box to dismiss)

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

SELECT TOP 100
  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
FROM
  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
WHERE (
  ( -- It must me marked as duplicated
    history.Id IN (
      SELECT TOP 1
        h.Id
      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
          p.Id
        FROM Posts p
        WHERE ( -- Is answer of the correct question
          p.ParentId = questions.Id
        )
        ORDER BY p.Score DESC, p.Id DESC
      )
    )
  )
)

view current version

edited aug 24 13

created aug 24 13 anonymous