December 28, 2014

Find rank of user(s) using mysql query

SELECT 1 + (
SELECT count( * )
FROM user a
WHERE a.balance > b.balance ) AS rank,id
FROM user b
ORDER BY rank;


table structure
---------------

id balance
1 0.50
2 0.25
3 0.10
4 1.20


output
------
rank id
1 4
2 1
3 2
4 3


if you want get rank of a specific user use where clause like this

SELECT 1 + (
SELECT count( * )
FROM user a
WHERE a.balance > b.balance ) AS rank,id
FROM user b WHERE id='1'
ORDER BY rank;





No comments:

Post a Comment