Get row position in MYSQL query [duplicate]

Question

Possible Duplicate:
ROW_NUMBER() in MySQL

Is this possible? I want to get a bunch of results in a specific order and then find out the position of a row by its id, for example:

1, foo
2, bar
3, foobar

I want to get the position of 1 in Alphabetical order, it would return 2

Answer 1

SELECT id, name, rank FROM 
    (
    SELECT t.id, t.name,
        @rownum := @rownum + 1 AS rank
    FROM TABLE t, (SELECT @rownum := 0) r
    ORDER BY name ASC
    ) `selection` WHERE id=1

Modified from this answer >> ROW_NUMBER() in MySQL

Answer 2

Working answer (in MySQL):

If you have the following table "names":

+------+--------+
| id   | name   |
+------+--------+
|    1 | foo    |
|    2 | bar    |
|    3 | foobar |
+------+--------+

And you want to know where "foo" ranks alphabetically, then:

SELECT z.rank FROM (
    SELECT t.id, t.name, @rownum := @rownum + 1 AS rank
    FROM names t, (SELECT @rownum := 0) r
    ORDER BY name ASC
) as z WHERE id=1;

Will produce:

+------+
| rank |
+------+
|    2 |
+------+

The changes from @potNpan's solution are the addition of as z and the change from @rownum = @rownum + 1 to @rownum := @rownum + 1. Now it works :)