Determine position of first occurence of string in MySQL?

Question

For a table like this:

username        | time
---------------------------------------
animuson        | 0.678
aP*animuson     | 0.967
animuson        | 0.567
qykumsoy        | 0.876

I'm trying to find a way in MySQL to order these by times and then find the position where username occurs and username is only counted once. So, if I'm searching for username 'qykumsoy', it should return 2, because both 'animuson' times are faster, but only the fastest one counts, then 'qykumsoy' is the next fastest after that. I can get them grouped and ordered, but how do I find what position that username is at? I figured just doing this in MySQL might be faster than looping through all the results in PHP, correct me if I'm wrong.

What I've thought of so far:

SELECT * FROM `times` ORDER BY MIN(`time`) GROUP BY `username`

I'm not very well experienced with MySQL, only the basic stuff. Any ideas?

Answer 1

What you're after is a rank with relation to the data in the table - MySQL doesn't have any ranking/analytic/windowing functionality but you have two options - this:

SELECT x.rank
   FROM (SELECT t.username, 
                         t.time,
                         (SELECT COUNT(*)
                              FROM TABLE y
                             WHERE y.time <= t.time) AS rank
                 FROM TABLE t) x
 WHERE x.username = 'qykumsoy'

...or using a variable:

SELECT x.rank
   FROM (SELECT t.username, 
                         t.time,
                         @rownum := @rownum + 1 AS rank
               FROM TABLE t
                  JOIN (SELECT @rownum := 0) r
         ORDER BY t.time DESC) x
 WHERE x.username = 'qykumsoy'