After sorting the rank, how to select one record

December 6, 2021

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?

>Solution :

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'