I have a table as follows:

PlayerID HR
john 40
mike 65
jake 32

I need to select the rank based on an HR input.

I have this query so far:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank,SUM(HR) AS HR FROM batregular GROUP BY PlayerID ORDER BY HR DESC LIMIT 30

That returns the HR and rank perfectly. FYI, I use GROUPBY to sum the career totals, but I only list one row here for simplifcation sake.

The results come back as expected as :

rank HR
1 65
2 40
3 32


What I need is to be able to select the rank based on an HR input, say 40. I'm getting a subselect error saying I can only have one column in my subselect:

SELECT @rank as rfinal from batregular WHERE @rank = (SELECT @rank:=@rank+1 AS rank FROM `batregular` GROUP BY PlayerID ORDER BY HR DESC LIMIT 30)

I'm not even sure that this is the right approach.

Figured it out.


SET @rank =0;
SELECT rank
FROM (
SELECT @rank := @rank +1 AS rank, PlayerID, SUM( HR ) AS HR
FROM batregular
GROUP BY PlayerID
ORDER BY HR DESC
LIMIT 30
)X
WHERE X.PlayerID = 'john'

You're probably the local SQL guru now... <g>