Questions with accepted or most ranked answers.

0

Please login or register to vote for this query.

(click on this box to dismiss)

CiviCRM Meta

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

SELECT 
    q.Id AS QuestionId,
    q.Title AS QuestionTitle,
    q.Body AS QuestionBody,
    q.Tags,
    q.Score AS QuestionScore,
    q.ViewCount,
    q.AnswerCount,
    q.CreationDate AS QuestionCreationDate,
    CONCAT('https://dba.stackexchange.com/questions/', q.Id) AS QuestionLink,
    a.Id AS AnswerId,
    a.Body AS AnswerBody,
    a.Score AS AnswerScore,
    a.CreationDate AS AnswerCreationDate,
    CONCAT('https://dba.stackexchange.com/a/', a.Id) AS AnswerLink
FROM 
    Posts q
OUTER APPLY
    (
        SELECT TOP 1 *
        FROM Posts a
        WHERE 
            a.ParentId = q.Id
            AND a.PostTypeId = 2 -- Answers
            AND a.Score >= 0 -- Exclude negatively scored answers
            AND a.CommunityOwnedDate IS NULL -- Not community wiki
            AND a.DeletionDate IS NULL -- Not deleted
        ORDER BY 
            CASE WHEN a.Id = q.AcceptedAnswerId THEN 0 ELSE 1 END, -- Accepted answer first
            a.Score DESC, -- Then highest score
            a.CreationDate ASC
    ) a
WHERE 
    q.PostTypeId = 1 -- Questions
    AND q.ClosedDate IS NULL -- Not closed
    AND q.CommunityOwnedDate IS NULL -- Not community wiki
    AND q.DeletionDate IS NULL -- Not deleted
    AND q.CreationDate >= '2010-09-01' AND q.CreationDate < '2011-09-01' -- Date range
    AND q.LastEditDate < DATEADD(month, -3, GETDATE()) -- Untouched for at least 3 months
    AND LEN(q.Body) > 450 -- Questions longer than 450 chars
    AND q.Score >= 0 -- Non-negative score
    AND q.AnswerCount BETWEEN 1 AND 10 -- Questions with 1 to 10 answers
ORDER BY 
    NEWID()

Enter Parameters

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