1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

-- Top users per tag

declare @user_id int = ##UserID##;
declare @limit int = ##Limit##;
declare @tag_id int = (select id from tags where tagname = '##TagName##');

--select rank() over (order by score desc) "Rank", * from (
select top (@limit)
  rank() over (order by sum(answers.score) desc) "Rank",
  users.id "User Link"
  ,sum(answers.score) as "Score"
  ,(case when users.id = @user_id then '<<<<' else '' end)
from users
join posts answers on (users.id = answers.owneruserid)
join posts parent on (answers.parentid = parent.id)
join posttags on (parent.id = posttags.postid)
where posttags.tagid = @tag_id
and answers.posttypeid = 2  -- answers only
and upper( users.location) like upper('%##Location##%')
group by users.id
order by sum(answers.score) desc
;

fork query

edited dec 6 12

created dec 6 12 anonymous