Coder Perfect

MySQL get row position in ORDER BY


Using MySQL, create the following 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 is sorted by name, it looks like this:

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

How can I retrieve the current location of the Beta row by selecting it? I’m searching for a collection of results that looks 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.

Asked by leepowers

Solution #1

Use this:

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

…to achieve a one-of-a-kind position value This:

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

…will assign the same value to ties. IE: If two values are in second place, they’ll both have a position of 2 when the first query gives one of them a position of 2 and the other a position of 3…

Answered by OMG Ponies

Solution #2

This is the only option that comes to mind:

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

Answered by zerkms

Solution #3

If the question is basic but the size of the returned result set is likely to be enormous, consider splitting it into two inquiries.

The first question employs a narrow-down filtering criteria to only obtain data for that row, whereas the second query calculates the position using COUNT with the WHERE clause.

In your instance, for example.

Query 1:

WHERE name = ‘Beta’, SELECT * FROM tbl

Query 2:

WHERE name >= ‘Beta’, SELECT COUNT(1) FROM tbl

This solution is far more scalable than OMG Ponies’ approach in a table with 2M records.

Answered by Max

Solution #4

The other options appear to be too complex for me.

Let’s take a simple example: suppose you have a table with columns:

userid | points

and you want to get the row position (the user’s “ranking”) by sorting the userids by points, then use:

SET @row_number = 0;

    (@row_number:=@row_number + 1) AS num, userid, points

The row position is given by num (ranking).

If you have MySQL 8.0 or higher, you should consider using ROW NUMBER ()

Answered by Avatar

Solution #5

The number of rows “better” than the targeted row is represented by a row’s position in the table.

As a result, you must count the rows.

WHERE name’Beta’ IS COUNT(*)+1 FROM table

In the event of a tie, the highest rank is returned.

If you add another row with same name of “Beta” after the existing “Beta” row, then the position returned would be still 2, as they would share same place in the classification.

I hope this helps anyone who are looking for something similar in the future, as I believe the question’s author has already found a solution to his problem.

Answered by NVG

Post is based on