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.Id,
           p.Title,
           p.Body AS PostBody,
           p.Tags,
           p.CreationDate,
           '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.PostId,
           c.Id AS CommentId,
           c.text AS CommentBody,  -- Adjust this column name if needed
           '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.Id,
       fp.Title,
       fp.PostBody,
       fp.Tags,
       fp.CreationDate,
       fp.PostURL,
       bc.CommentId,
       bc.CommentBody,
       bc.CommentURL
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