MySQL get row position in ORDER BY

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...

This is the only way that I can think of:

SELECT `id`,       (SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,       `name`FROM `table`WHERE `name` = 'Beta'

If the query is simple and the size of returned result set is potentially large, then you may try to split it into two queries.

The first query with a narrow-down filtering criteria just to retrieve data of that row, and the second query uses COUNT with WHERE clause to calculate the position.

For example in your case

Query 1:

SELECT * FROM tbl WHERE name = 'Beta'

Query 2:

SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'

We use this approach in a table with 2M record and this is way more scalable than OMG Ponies's approach.