mysql how to get row position with where clause

Question

i have data like this

id  userid        phone
1   1000          11111
2   1001          22222
3   1000          33333
4   1000          44444
5   1001          55555
6   1000          66666

column phone is unique

i am expecting the following result

position phone
3        44444

i tried row_number but its not working.

select *,row_number() over 
    (partition by userid order by id asc) as position 
from table 
where phone=44444

this way position always 1

Answer 1

row_number() is applied on the result set according to the WHERE clause. Since you got only one record in that result, the row_number() is 1.

You first need to select the complete set, apply row_number() to it and then get the record with the searched number

SELECT position,
       phone
       FROM (SELECT row_number() OVER (PARTITION BY userid
                                       ORDER BY id ASC) AS position,
                    phone
                    FROM elbat) AS x
       WHERE phone = 44444;

If the table is large, it might be beneficial to limit the results of the derived table to the records of the user that has the searched number. An index on (phone, userid) and another one on (userid, id, phone) might support this.

SELECT position,
       phone
       FROM (SELECT row_number() OVER (ORDER BY id ASC) AS position,
                    phone
                    FROM elbat
                    WHERE userid = (SELECT userid
                                           FROM elbat
                                           WHERE phone = 44444)) AS x
       WHERE phone = 44444;