0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT DISTINCT
  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,
  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 (
  (
    questions.Id = 10478031
  ) AND (
    history.PostHistoryTypeId = 10
  ) AND (
    history.Text LIKE '{"OriginalQuestionIds":%'
  ) AND (
    (
      answers.Id IS NULL
    ) OR (
      answers.Id IN (
        SELECT TOP 1 p.Id
        FROM Posts p
        WHERE p.ParentId = questions.Id
        ORDER BY p.Score DESC
      )
    )
  )
)
ORDER BY questions.Id

fork query

edited aug 24 13

created aug 15 13 anonymous