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

Answer 2

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'

Answer 3

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.

Answer 4

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()

Answer 5

The position of a row in the table represents how many rows are "better" than the targeted row.

So, you must count those rows.

SELECT COUNT(*)+1 FROM table WHERE name<'Beta'

In case of a tie, the highest position 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.

Hope this helps people that will search for something similar in the future, as I believe that the question owner already solved his issue.

Answer 6

I've got a very very similar issue, that's why I won't ask the same question, but I will share here what did I do, I had to use also a group by, and order by AVG. There are students, with signatures and socore, and I had to rank them (in other words, I first calc the AVG, then order them in DESC, and then finally I needed to add the position (rank for me), So I did something Very similar as the best answer here, with a little changes that adjust to my problem):

I put finally the position (rank for me) column in the external SELECT

SET @rank=0;
SELECT @rank := @rank + 1 AS ranking, t.avg, t.name
  FROM(SELECT avg(students_signatures.score) as avg, students.name as name
FROM alumnos_materia
JOIN (SELECT @rownum := 0) r
left JOIN students ON students.id=students_signatures.id_student
GROUP BY students.name order by avg DESC) t 

Answer 7

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

Answer 8

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" = ?)

Answer 9

may be what you need is with add syntax

LIMIT

so use

SELECT * FROM tbl ORDER BY name ASC LIMIT 1

if you just need one row..