SELECT ct.pid, ct.betcount, ct.rownum, ou.vaddress FROM
(
SELECT a.pid,a.betcount,
CASE WHEN @rowtotal = a.betcount THEN @rownum
WHEN @rowtotal := a.betcount THEN @rownum := @rownum + 1
WHEN @rowtotal = 0 THEN @rowtotal :=@rownum + 1
END AS rownum FROM
(SELECT rpid AS pid, COUNT(pid) AS betcount FROM one_bet WHERE rpid <> 0 AND rid = 1 GROUP BY rpid ORDER BY betcount DESC) AS a,
(SELECT @rownum := 0 ,@rowtotal := NULL) r
) ct LEFT JOIN one_user ou ON ct.pid = ou.pid;