0

Please login or register to vote for this query.

(click on this box to dismiss)

Show the user having the higher score for 50 top tags

Q&A for pro webmasters

-- User by tags
-- Show the user having the higher score for 50 top tags

declare @temp table (TagId int, TagName char(255), OwnerUserId int, DisplayName nvarchar(80), Score int);

declare @tagsId int
declare CustList cursor for select top 50 tagid from posttags group by tagid order by count(*) desc
OPEN CustList
FETCH NEXT FROM CustList INTO @tagsId
WHILE @@FETCH_STATUS = 0
BEGIN
  insert @temp (TagId, TagName, OwnerUserId, DisplayName, Score) 
  select top 1 @tagsId as TagId, t.TagName, p.OwnerUserId as UsersId, u.DisplayName, sum(p.Score) as Score from PostTags pt, Posts p, Users u, Tags t where pt.TagId = @tagsId and pt.PostId=p.ParentId and p.OwnerUserId=u.id and pt.TagId=t.id
  group by u.DisplayName, p.OwnerUserId, t.TagName
  order by sum(p.Score) desc
  FETCH NEXT FROM CustList INTO @tagsId
END
CLOSE CustList
DEALLOCATE CustList

select * from @temp

fork query

created jun 19 10 radius