I can’t get specific row’s postition in table using order by in MySQL

I want to get position of specific row in MySQL table using operation ‘order by’. Let’s say i have this table:

And now i want to order them by user_points (ascending) and get Ann’s position in here. The output I desire is ‘3’. I was trying to do it, but with no results… If anyone has any idea how to do it I’d be grateful.

Answer

One method is:

select count(*) + 1
from t
where t.user_points < (select t2.user_points
                       from t t2
                       where t2.name = 'Ann'
                      );

This is equivalent to the window function rank():

select t.*, rnk
from (select t.*, rank() over (order by score) as rnk
      from t
     ) t
where name = 'Ann';

But with the right indexes, the first version might be a little bit faster.