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()