SQLite – Sort and get the number in the list

sql.all(`SELECT * FROM users ORDER BY points DESC`).then()

How can I sort the list and get the current number on the list? Example:

LIST:
USERID | POINTS
1 | 20
2 | 100
3 | 67

VAR MYID = 1

Output: your the number 3 in the list

Answer

If you just want 3 as a result, you can do:

select 1 + count(*) pos
from mytable t
where points > (select points from mytable t1 where userid = :myid)

Alternatively, you can use window functions:

select pos
from (select t.*, rank() over(order by points) pos from mytable) t
where userid = :myid