select s.*,
CASE
WHEN rank >= 1 and s.rank <= 10 THEN 1
WHEN rank >= 11 and s.rank <= 20 THEN 2
WHEN rank >= 21 and s.rank <= 30 THEN 3
WHEN rank >= 31 THEN 0
END as AccountType
FROM
(
SELECT u.ID, @rownum := @rownum + 1 AS rank
FROM
Users u,
(SELECT @rownum := 0) r
ORDER BY u.AverageROI desc
) as s