0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select 
Q.Id as [Post Link],
Q.Score,
Q.ViewCount,
ShortLInkAnswers.Count as NumShortLinkAnswers
from
 Posts as Q
 inner join PostHistory H ON H.PostId = Q.Id
 inner join 
 (select
 count(*) as Count,
 A.ParentID
 from
 Posts as A 
 where
 A.posttypeid = 2
 and A.body like '%http://%' 
 and len(A.body) between 40 and 80
 group by A.ParentID
 ) as ShortLinkAnswers on ShortLinkAnswers.ParentID = Q.Id
where
 Q.posttypeid = 1
 and Q.ClosedDate is null
 and ShortLinkAnswers.count >= 0 
 AND Q.Score <= 25
group by
 Q.Id,
 Q.Score,
 Q.ViewCount,
 ShortLInkAnswers.Count
having 
 SUM(CASE WHEN H.PostHistoryTypeId = 14 THEN -1 WHEN H.PostHistoryTypeId = 15 then 1 ELSE 0 END) >= 0
order by 
 Q.score desc, 
 Q.ViewCount Desc

fork query

edited feb 22 13 badp

created feb 19 13 George W Bush