0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

WITH TopQuestions AS (
    SELECT TOP(300) Id, ViewCount, Title, Body, Score, CreationDate, Tags, AnswerCount, CommentCount, FavoriteCount
    FROM Posts
    WHERE PostTypeId = 1 AND CreationDate >= DATEADD(MONTH, -18, GETDATE())
    ORDER BY ViewCount DESC, Score DESC
), RankedAnswers AS (
    SELECT
        Posts.Id,
        Posts.ParentId AS QuestionId,
        Posts.Body,
        Posts.Score,
        Posts.CreationDate,
        ROW_NUMBER() OVER(PARTITION BY Posts.ParentId ORDER BY Posts.Score DESC, Posts.CreationDate DESC) AS AnswerRank
    FROM Posts
    WHERE PostTypeId = 2 AND Posts.ParentId IN (SELECT Id FROM TopQuestions)
)
SELECT q.Id AS QuestionId, q.ViewCount, q.Title, q.Body AS QuestionBody, q.Score AS QuestionScore, q.CreationDate AS QuestionDate, q.Tags, q.AnswerCount, q.CommentCount, q.FavoriteCount, 
       a.Id AS AnswerId, a.Body AS AnswerBody, a.Score AS AnswerScore, a.CreationDate AS AnswerDate
FROM TopQuestions q
JOIN RankedAnswers a ON q.Id = a.QuestionId
WHERE a.AnswerRank <= 5
ORDER BY q.ViewCount DESC, q.Score DESC, a.AnswerRank;

fork query

created apr 1 anonymous