CODEWITHSUNDEEP
sqlpostgresql
eatkimchi
eatkimchi

Reputation: 59

How do I access the rank

I'm tryiing to run this query in Postgresql. I get the results I want, but I'm trying to limit the results to a value in a column. However, it doesn't let me access the column:

select count(event), event, u.userid, u.campaign_id, 
rank() over (partition by u.campaign_id order by count(event) desc) as THISHERE 
from events e join users u on u.userid = e.userid 
where THISHERE=1 
group by event, 3 , 4 
order by 1 desc 
limit 20;

ERROR: column "thishere" does not exist

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You need to use a subquery:

select eu.*
from (select count(event), event, u.userid, u.campaign_id, 
             rank() over (partition by u.campaign_id order by count(event) desc) as THISHERE 
      from events e join
           users u
           on u.userid = e.userid 
      group by event, 3 , 4 
     ) eu
where THISHERE = 1 
order by 1 desc 
limit 20;

I think you can also do what you want using distinct on:

select distinct on (campaign_id) count(event), event, u.userid, u.campaign_id, 
             rank() over (partition by u.campaign_id order by count(event) desc) as THISHERE 
from events e join
     users u
     on u.userid = e.userid 
group by event, 3 , 4
order by campaign_id, count(event) desc
fetch first 20 rows only;

Upvotes: 1

Related Questions