MySQL rank query. Get the position of a specific member

Question

I have the following table on my DataBase: TimeRank(user-id, name, time)

I would like to order the table by time and get the position of an specific ID on the table, for example:

The user nº 68 is on the 3rd position.

I only need to do a query that returns the position of the user.

MySQL don't have the function row_number, so I don't know how to do it.

Edit your question and provide sample data and desired results. — Gordon Linoff, Mar 15 '18 at 12:11
https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select — ecp, Mar 15 '18 at 13:44

score 1 · Accepted Answer · answered Mar 15 '18 at 13:41

SELECT x.user-id, 
       x.name,
       x.time,
       x.position
  FROM (SELECT t.user-id,
               t.name,
               t.time,
               @rownum := @rownum + 1 AS position
          FROM TABLE TimeRank t
          JOIN (SELECT @rownum := 0) r
          ORDER BY t.time) x
 WHERE x.user-id = 123

Alternative:

SELECT user-id,
       (SELECT COUNT(*) FROM TimeRank WHERE time <= (SELECT time FROM TimeRank WHERE user-id = 123)) AS position,
       time,
       name
FROM TimeRank
WHERE user-id = 123

score 0 · Answer 2 · answered Mar 15 '18 at 13:45

You can generate a position column with a variable

set @pos=0;

select pos,user_id
from (select @pos:=@pos+1 pos,user_id from TimeRank order by time) s
where user_id=68;

If indexing is a concern, you can add a column to your table and update it with

set @pos=0;

update TimeRank set position=(@pos:=@pos+1) order by time;

MySQL rank query. Get the position of a specific member

2 Answers2