sqlite get index of specific row after sorting-CodePudding

I have an sqlite table called scores that has the columns id (PK) and score. Now I have an id and want to know at wich 'place' that person would be. A way to do that would be just select * from scores order by score desc and then look at the index of my id via code.

But I dont want to select the whole table if I just want to know the 'place' of an id so is it possible to get the place/index of a row after sorting it by score?

CodePudding user response：

You could use RANK here:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY score DESC) rnk
    FROM scores
)

SELECT id, rnk
FROM cte
WHERE id = <some value>;

Note that if you don't expect any duplicates, then ROW_NUMBER could be swapped for RANK above. If you do expect duplicates, then DENSE_RANK might also make sense instead of RANK.