0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for anime and manga fans

WITH PPosts AS
(
SELECT Posts.ID, Posts.PostTypeId, Posts.CreationDate, Posts.Score, Posts.ViewCount, Posts.OwnerUserId, Posts.LastActivityDate,Posts.AnswerCount, Posts.CommentCount, Posts.FavoriteCount FROM Posts
WHERE (Posts.CreationDate between '2012-03-01 11:30:01' and '2012-03-01 12:00')
), 
Tagi AS(
SELECT Posts.ID, Posts.PostTypeId, Posts.CreationDate, Posts.Score, Posts.ViewCount, Posts.OwnerUserId, Posts.LastActivityDate, Posts.AnswerCount, Posts.CommentCount, Posts.FavoriteCount, Tags.id AS TagId
FROM Posts, tags
where tags.id in (select top 1 posttags.tagid from posttags join tags on posttags.tagid = tags.id
where PostTags.postid = Posts.id
order by tags.count desc)
),
posty as(
select PPosts.ID, PPosts.PostTypeId, PPosts.CreationDate, PPosts.Score, PPosts.ViewCount, PPosts.OwnerUserId, PPosts.LastActivityDate, PPosts.AnswerCount, PPosts.CommentCount, PPosts.FavoriteCount, tagi.TagId 
from PPosts left join tagi on PPosts.id=tagi.id
)

select tags.Id, tags.TagName, tags.Count
from tags
where tags.Id in 
(select tagid from posty)

fork query

edited may 7 12

created may 7 12 anonymous