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 FilteredPosts AS (
    SELECT p.*,
           'https://stackoverflow.com/questions/' + CAST(p.Id AS VARCHAR(20)) AS PostURL,
           ROW_NUMBER() OVER (ORDER BY p.CreationDate DESC) AS RowNum
    FROM Posts p
    JOIN PostTags pt ON p.Id = pt.PostId
    JOIN Tags t ON pt.TagId = t.Id
    WHERE t.TagName = 'eclipse'
    AND p.ClosedDate IS NOT NULL  -- Ensure that posts are closed
),
BestComments AS (
    SELECT c.*,
           'https://stackoverflow.com/posts/comments/' + CAST(c.Id AS VARCHAR(20)) AS CommentURL
    FROM Comments c
    JOIN Posts p ON c.PostId = p.Id
)
SELECT fp.*, bc.*
FROM FilteredPosts fp
LEFT JOIN BestComments bc ON fp.Id = bc.PostId
ORDER BY fp.CreationDate DESC;

fork query

edited aug 14 anonymous

created aug 12 jon.doe44985