0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

WITH TopQuestions AS (
    SELECT 
        Id,
        Title,
        CreationDate,
        Score
    FROM Posts
    WHERE PostTypeId = 1 -- Question
        AND DATEDIFF(year, CreationDate, GETDATE()) <= 5
    ORDER BY Score DESC, CreationDate DESC
    OFFSET 30000 ROWS
    FETCH NEXT 100000 ROWS ONLY
),
BestAnswers AS (
    SELECT
        ParentId,
        MAX(Score) AS MaxScore
    FROM Posts
    WHERE PostTypeId = 2 -- Answer
    GROUP BY ParentId
)
SELECT
    TQ.Id AS Id,
    TQ.Title AS Question,
    TQ.CreationDate AS QuestionDate,
    TQ.Score AS Ranking,
    P.Body AS Answer
FROM TopQuestions TQ
JOIN BestAnswers BA ON TQ.Id = BA.ParentId
JOIN Posts P ON P.ParentId = BA.ParentId AND P.Score = BA.MaxScore
ORDER BY TQ.Score DESC, TQ.CreationDate DESC;

fork query

edited sep 25 23

created mar 23 23 anonymous