Using MySQL, how do I select query result rank of one particular row?

Question

I've spent quite a lot of time today trying various things, but none of them seem to work. Here's my situation, I'd like to be able to select the rank of a row based on it's ID from a specifically sorted row

For example, if my query is something like:

SELECT id, name FROM people ORDER BY name ASC

with results like:

id   name
3    Andrew
1    Bob
5    Joe
4    John
2    Steve

I would like to get the rank (what row it ends up in the results) without returning all the rows and looping throw them until I get to the one I want (in PHP).

For example, I'd like to select the 'rank' of 'Steve' so that it returns -- in this case -- 5 (not his id, but the 'rank' of his name in the above query).

Similarly, I'd like to be able to select the rank of whatever row has the ID of 1. For this example, I'd like to return a 'rank' of 2 (because that's in what result row there is an ID of 1) and nothing else.

I've Google'd as much as I could with varying results... either having really slow queries for larger tables or having to create all sorts of temporary tables and user variables (the former I'd REALLY like to avoid, the latter I suppose I can live with).

Any help or insight would be greatly appreciated.

Answer 1

Something like this?

SELECT Row, id, name
FROM (SELECT @row := @row + 1 AS Row, id, name
      FROM people
      ORDER BY name ASC)
WHERE Row = @SomeRowNumber

If you want to go by the ID, just alter the where clause.

Answer 2

Try this:

SELECT @rownum:=@rownum+1 `rank`, p.id, p.name
FROM people p, (SELECT @rownum:=0) r
ORDER BY name ASC

Answer 3

from artfulsoftware:

SELECT p1.id, p1.name, COUNT( p2.name ) AS Rank
    FROM people p1
    JOIN people p2 
    ON p1.name < p2.name
    OR (
         p1.name = p2.name
         AND p1.id = p2.id
    )
GROUP BY p1.id, p1.name
ORDER BY p1.name DESC , p1.id DESC
LIMIT 4,1