0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

WITH
    Answers AS (
        SELECT *
            FROM Posts
            WHERE PostTypeId = 2 -- Answers
    )
SELECT TOP 10
        Questions.Id AS [Post Link],
        'site://a/' + CAST(FirstAnswer.Id AS NVARCHAR) + '|' + CAST(DATEDIFF(mi, Questions.CreationDate, FirstAnswer.CreationDate) AS NVARCHAR) AS [First Answer],
        'site://a/' + CAST(LastAnswer.Id AS NVARCHAR) + '|' + nchar(0x2713) AS [Last Answer],
        'site://a/' + CAST(AcceptedAnswer.Id AS NVARCHAR) + '|' + nchar(0x2713) AS [Accepted Answer],
        'site://a/' + CAST(BestAnswer.Id AS NVARCHAR) + '|' + nchar(0x2713) AS [Highest-Scored Answer]
    FROM Posts AS Questions
        LEFT OUTER JOIN Answers AS FirstAnswer
            ON FirstAnswer.ParentId = Questions.Id
        LEFT OUTER JOIN Answers AS LastAnswer
            ON LastAnswer.ParentId = Questions.Id
        LEFT OUTER JOIN Answers AS AcceptedAnswer
            ON AcceptedAnswer.Id = Questions.AcceptedAnswerId
        LEFT OUTER JOIN Answers AS BestAnswer
            ON BestAnswer.Id = Questions.Id
    WHERE
        Questions.PostTypeId = 1 -- Questions
        AND FirstAnswer.Id = (
            SELECT TOP 1 Id
                FROM Answers
                WHERE
                    Answers.ParentId = Questions.Id
                ORDER BY CreationDate ASC
        )
        AND LastAnswer.Id = (
            SELECT TOP 1 Id
                FROM Answers
                WHERE
                    Answers.ParentId = Questions.Id
                ORDER BY CreationDate DESC
        )
        AND BestAnswer.Id = (
            SELECT TOP 1 Id
                FROM Answers
                WHERE
                    Answers.ParentId = Questions.Id
                ORDER BY Score DESC
        );

view current version

edited nov 19 13

created nov 19 13 200_success