After sorting the rank, how to select one record-CodePudding

I'm working with my database. And I want to sort by download and rank it. After that I want to select one record by id.

I know how to rank it.

SELECT id, RANK() OVER(ORDER BY "downLoad" DESC) rank FROM gameinfo;

So I try to use 'WHERE' to get one result

SELECT id, RANK() OVER(ORDER BY "downLoad" DESC) rank FROM gameinfo WHERE id='9b9df0c5-9906-4444-b30a-9b64ff8fea94';

I got one record but the result was rank=1 I expect 2.

Right now I get all the gameinfo and ranked it all. After that, I search the id from JavaScript to get the right rank, but is there a way just using SQL to get one record?

CodePudding user response：

the WHERE is evaluated before your window function ranks the records resulting in only one record to rank. You could put the SELECT in a subquery and do a WHERE over the main query to keep all records in your dataset.

SELECT * 
FROM
       (
           SELECT id, RANK() OVER(ORDER BY "downLoad" ASC) rank 
           FROM gameinfo
        ) a 
WHERE ID = '9b9df0c5-9906-4444-b30a-9b64ff8fea94'