0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and scholars of mythology and folklore

WITH CTE AS (

SELECT p.id                                                     
FROM   Posts p
        LEFT JOIN PostHistory h
        ON h.PostId = p.Id
              AND h.CreationDate = p.ClosedDate
              AND PostHistoryTypeId = 10
              AND Comment = '1'
        
WHERE  
       h.id IS NULL
       AND p.ClosedDate IS NOT NULL -- closed
       AND ( p.Score >= 0 OR  p.AnswerCount > 0)) -- either not downvoted, or has answers
SELECT 
      p.Score,
       p.Id                                                                ,
       CASE WHEN p.AnswerCount = 0 THEN NULL ELSE p.AnswerCount END          AS Answers,
       CASE WHEN p.AcceptedAnswerId   IS NOT NULL THEN 'Yes' ELSE NULL END AS Accepted,
       CASE WHEN p.CommunityOwnedDate IS NOT NULL THEN 'Yes' ELSE NULL END AS Wiki,
       p.CommentCount                                                      AS Comments,
       p.ViewCount                                                         AS Views,
       p.OwnerUserId  
FROM
      posts p
      INNER JOIN CTE on p.id = CTE.ID
ORDER  BY  p.Score ASC,  p.CreationDate DESC

fork query

edited mar 21 12 conradfrix

created mar 16 12 e