i have a table like this:
id money
210 1000
321 1300
121 980
i want to know on witch place is 321
or any other id, so, in my case, i should have 321 is on 1'st place
not sure if i can do this only with mysql..
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id, money
FROM table
ORDER BY money DESC
SELECT q.id,
q.money,
@curRow := @curRow +1,
CONCAT( q.id, ' is on ', @curRow , ' place ' ) AS a
FROM (
SELECT 210 AS id, 1000 AS money
UNION
SELECT 321 AS id, 1300 AS money
UNION
SELECT 121 AS id, 980 AS money
)q
JOIN (
SELECT @curRow :=0
)r
ORDER BY q.money DESC
UPDATE
SELECT *
FROM (
SELECT q.id,
q.money, @curRow := @curRow +1,
CONCAT( q.id, ' is on ', @curRow , ' place ' ) AS a
FROM (
SELECT 210 AS id, 1000 AS money
UNION
SELECT 321 AS id, 1300 AS money
UNION
SELECT 121 AS id, 980 AS money
)q
JOIN (
SELECT @curRow :=0
)r
ORDER BY q.money DESC
)q1
WHERE q1.id =210
精彩评论