CODEWITHSUNDEEP
sqlpostgresql
Mark Reddington
Mark Reddington

Reputation: 153

Filter Postgres percent_rank before calculation but show all results?

I am trying to figure out how to run a percent_rank on a table, but filter which records the percent_rank is run on, but still include those rows that were filtered out, but give them a 0 percent_rank

For example, I have a users table and everyone has a point value assigned to them. I only want to percent_rank on people with >= 20 points, but not exclude them from the results. Meaning if they have 19 points I can still see their record, but their rank is 0

For example:

SELECT name,points,PERCENT_RANK() OVER (ORDER BY points) 
FROM users 
WHERE points >= 20;

But keep the people with less than 20 points in the results.

Upvotes: 2

Views: 312

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can do this with case:

SELECT u.name, u.points,
       (CASE WHEN u.points >= 20
             THEN PERCENT_RANK() OVER (PARTITION BY u.points >= 20 ORDER BY u.points) 
             ELSE 0
        END) as rank
FROM users u;

If you don't want to repeat the condition, you can use a lateral join:

SELECT u.name, u.points,
       (CASE WHEN v.cond
             THEN PERCENT_RANK() OVER (PARTITION BY v.cond ORDER BY points) 
             ELSE 0
        END) as rank
FROM users u CROSS JOIN LATERAL
     (VALUES (u.points >= 20)) v(cond);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

You could use a union here:

SELECT name, points, 0 AS pct_rank FROM users WHERE points < 20
UNION ALL
SELECT name, points, PERCENT_RANK() OVER (ORDER BY points) FROM users WHERE points >= 20;

Upvotes: 2

Related Questions