SQL Rank condition

Admin 1
Viewed 121

I am running this code to select the rank for each database in sql. However, I am not getting the result I want.

DATABASE: CODE:

SELECT user_id,country,city,institute,  
RANK () OVER (PARTITION BY country ORDER BY up_vote+down_vote DESC) country_rank,
RANK() OVER (PARTITION BY city ORDER BY up_vote+down_vote DESC) city_rank,
RANK() OVER (PARTITION BY institute ORDER BY up_vote+down_vote DESC) institute_rank
FROM Users ;

FROM Users;
 user_id | country |  city   |      institute       | country_rank | city_rank | institute_rank
---------+---------+---------+----------------------+--------------+-----------+----------------
      17 | Canada  | Toronto | University of Ottawa |            1 |         1 |              1
      18 | Canada  | Ottawa  | University of Ottawa |            2 |         1 |              2
      16 | test123 | test123 | test123              |            1 |         1 |              1

I just want to get the user_id=18 so I added WHERE user_id=18

However, I get this as my result

 user_id | country |  city  |      institute       | country_rank | city_rank | institute_rank
---------+---------+--------+----------------------+--------------+-----------+----------------
      18 | Canada  | Ottawa | University of Ottawa |            1 |         1 |              1

What I want as my outcome

 user_id | country |  city  |      institute       | country_rank | city_rank | institute_rank
---------+---------+--------+----------------------+--------------+-----------+----------------
      18 | Canada  | Ottawa | University of Ottawa |            2 |         1 |              2
rank window-functions postgresql sql
1 Answers

Putting a where clause directly in the query removes rows from the dataset before the ranking functions get a chance to execute. There is only one row left, so it is ranked first in every partition.

You would need to rank in a subquery, then filter in an outer query:

SELECT *
FROM (
    SELECT 
        user_id,
        country,
        city,
        institute,  
        RANK() OVER (PARTITION BY country ORDER BY up_vote+down_vote DESC) country_rank,
        RANK() OVER (PARTITION BY city ORDER BY up_vote+down_vote DESC) city_rank,
        RANK() OVER (PARTITION BY institute ORDER BY up_vote+down_vote DESC) institute_rank
    FROM Users
) t
WHERE user_id = 18
user_106767160
Related