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.

Laborsaving

#2520857 – Tapeworm

Possible duplicate of MySQL - Get row number on select – Thermodynamics

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

Reunion

@actual: There's nothing to say - there's no alternative, other than moving to a competitor that supports analytic functions (PostgreSQL, Oracle, SQL Server, DB2...) – Reunion

@OMGPonies Just forget a comma after position, but it's perfect. – Subdelirium

I know it's quite an old post, but I need a similar solution. However, when using inner joins, group by and order by, the "position" field ignores these and the value is all mixed up. Any solutions? – Starshaped

@Starshaped you should ask a new question and perhaps refer to this one. – Effusive

@actual, since this is a query for a single row, there shouldn't be a significant performance concern here. It's different if you're trying to get the ranks of a full list, but you could just "cheat" and use an implicit rank by ordering by points. – Clyburn

I think it could be solved by using ROW_NUMBER() function to calculate position. – Reremouse

"FROM TABLE" should be just "FROM" @AlexJolig – Rockwell

If you want to get the position taking into account the ties, this is not completely right. By using <=, if you have ["a","b","b","c"] you'll get the positions [1,3,3,4] instead of what is usually expected: [1,2,2,4] (you wouldn't get what the answer says: [1,2,2,3]). To get [1,2,2,4] you should have < without the = and if you want to start at 1 you should use ... COUT(*)+1... – Incorrect

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'

Cloutier

+1 Nice trick... However you'd probably want to use name <= 'Beta' instead – Gateshead

This approach will give the same position values for ties. – Reunion

(Deleted my previous comment - I was wrong)... What if you add a LIMIT 1 in there? In case of a tie, you'd be getting just one row with the last position of the tie. – Gateshead

If OP can guarantee that name field is unique - then there is no reason to make query more complex. If he can't - then let's wait for his result expectations for tied names. – Cloutier

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.

Masque

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

Coagulum

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.

Cythera

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 

Ferullo

This answer was easier to understand than the accepted one. +1 – Meatiness

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

Clementia

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

Burning

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

Animadversion

this answer don't solve the problem here. You could consider deleting it – Ferullo