9

Please login or register to vote for this query.

(click on this box to dismiss)

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

-- Compare me against the most frequent voters

declare @my_id int = ##UserId##;

declare @temp table(
  id int,
  votes int,
  up int,
  down int,
  me text
);

insert into @temp (id, votes, up, down)
select top ##Limit##
  id,
  upvotes + downvotes "votes",
  upvotes,
  downvotes
from users
where id != -1  -- community user
  and id != @my_id  -- selected below
  and reputation > 1
order by (upvotes + downvotes) desc;

insert into @temp(id, votes, up, down, me)
select
  id,
  upvotes + downvotes "votes",
  upvotes,
  downvotes,
  '>'
from users
where id = @my_id;

declare @last int = round((select min(votes) from @temp),0);
-- apparently min(votes) is a varchar?! and I don't know how to cast in t-sql
declare @top int = (case when (select votes from @temp where id = @my_id) > @last then ##Limit## else ##Limit## + 1 end)

select top (@top) 
  me,
  (case when (votes > @last) then (rank() over (order by votes desc)) else null end),
  id "User Link", votes "Votes",
  (case when up + down > 0 then (100 * up / (up + down)) else null end) "% up",
  up "Upvotes", down "Downvotes"
from @temp order by votes desc;

fork query

created oct 31 10 Roger Pate