MySQL get row position in ORDER BY

user_212700 0
Viewed 96315

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.

analytic-functions mysql sql
9 Answers

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.

user_12565660

The other answers seem too complicated for me.

Here comes an easy example, let's say you have a table with columns:

userid | points

and you want to sort the userids by points and get the row position (the "ranking" of the user), then you use:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, userid, points
FROM
    ourtable
ORDER BY points DESC

num gives you the row postion (ranking).

If you have MySQL 8.0+ then you might want to use ROW_NUMBER()

user_10662340

I was going through the accepted answer and it seemed bit complicated so here is the simplified version of it.

SELECT t,COUNT(*) AS position FROM t      
 WHERE name <= 'search string' ORDER BY name
user_73928540

I have similar types of problem where I require rank(Index) of table order by votes desc. The following works fine with for me.

Select *, ROW_NUMBER() OVER(ORDER BY votes DESC) as "rank"
From "category_model"
where ("model_type" = ? and "category_id" = ?)
user_81368030
Related