WITH BestAnswers AS ( SELECT Q.Id AS QuestionId, Q.Title ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH BestAnswers AS (
    SELECT 
        Q.Id AS QuestionId,
        Q.Title AS QuestionTitle,
        Q.Body AS QuestionBody,
        A.Id AS AnswerId,
        A.Body AS AnswerBody,
        A.Score AS AnswerScore,
        A.Score + ISNULL(PF.VoteCount, 0) AS AdjustedScore,
        ROW_NUMBER() OVER(PARTITION BY Q.Id ORDER BY A.Score DESC) AS Rank,
        ROW_NUMBER() OVER(PARTITION BY Q.Id ORDER BY A.Score + ISNULL(PF.VoteCount, 0) DESC) AS AdjustedRank
    FROM
        Posts Q
    JOIN
        Posts A ON A.ParentId = Q.Id
    LEFT JOIN
        (SELECT PostId, COUNT(*) AS VoteCount
         FROM PostFeedback
         WHERE VoteTypeId = 2
         GROUP BY PostId) PF ON PF.PostId = A.Id
    WHERE
        Q.PostTypeId = 1 AND
        Q.AnswerCount >= 2
)
SELECT TOP 100
    QuestionId,
    QuestionTitle,
    QuestionBody,
    AnswerId AS BestAnswerId,
    AnswerBody AS BestAnswerBody,
    AnswerScore AS BestAnswerScore,
    AdjustedAnswerId,
    AdjustedAnswerBody,
    AdjustedScore
FROM
    (SELECT 
        QuestionId,
        QuestionTitle,
        QuestionBody,
        AnswerId,
        AnswerBody,
        AnswerScore
     FROM
        BestAnswers
     WHERE
        Rank = 1) AS BestAnswers
JOIN
    (SELECT 
        QuestionId AS AdjustedQuestionId,
        AnswerId AS AdjustedAnswerId,
        AnswerBody AS AdjustedAnswerBody,
        AdjustedScore
     FROM
        BestAnswers
     WHERE
        AdjustedRank = 1) AS AdjustedBestAnswers ON BestAnswers.QuestionId = AdjustedBestAnswers.AdjustedQuestionId
ORDER BY
    NEWID()

Enter Parameters

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