MySQL get row position in ORDER BY

Question

With the following MySQL table:

+-----------------------------+ + id INT UNSIGNED             + + name VARCHAR(100)           + +-----------------------------+ 

How can I select a single row AND its position amongst the other rows in the table, when sorted by name ASC. So if the table data looks like this, when sorted by name:

+-----------------------------+ + id | name                   + +-----------------------------+ +  5 | Alpha                  + +  7 | Beta                   + +  3 | Delta                  + + .....                       + +  1 | Zed                    + +-----------------------------+ 

How could I select the Beta row getting the current position of that row? The result set I'm looking for would be something like this:

+-----------------------------+ + id | position | name        + +-----------------------------+ +  7 |        2 | Beta        + +-----------------------------+ 

I can do a simple SELECT * FROM tbl ORDER BY name ASC then enumerate the rows in PHP, but it seems wasteful to load a potentially large resultset just for a single row.

Answer 1

Use this:

SELECT x.id,         x.position,        x.name   FROM (SELECT t.id,                t.name,                @rownum := @rownum + 1 AS position           FROM TABLE t           JOIN (SELECT @rownum := 0) r       ORDER BY t.name) x  WHERE x.name = 'Beta' 

...to get a unique position value. This:

SELECT t.id,        (SELECT COUNT(*)           FROM TABLE x          WHERE x.name <= t.name) AS position,        t.name       FROM TABLE t        WHERE t.name = 'Beta' 

...will give ties the same value. IE: If there are two values at second place, they'll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other...