0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators and users of the CiviCRM Constituent Relationship Management software

WITH TopQuestions AS (
    SELECT TOP(1000) Id, ViewCount, Title, Body, Score, CreationDate, Tags, AnswerCount, CommentCount, FavoriteCount
    FROM Posts
    WHERE PostTypeId = 1
    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

edited feb 27

created feb 20 anonymous