sqlmysqlanalytic-functions

MySQL get row position in ORDER BY


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.


Solution

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