3

I need help with my query. I want to add a row_number to partition my fields, but I get the error: ERROR: column "rn" does not exist LINE 22: and rn <= 3
Do you find something strange in my query? Thanks a lot!

with location as 
(select location, topcount
from pr.rankinglist_location
where topcount = 3
or (topcount = 10 and population_all > 150000)
or topcount = 25)

select store_displayname as restaurant_name,
    street,
    street_no,
    zipcode,
    city, 
    topcount,
    ROW_NUMBER() OVER (PARTITION BY city
                              ORDER BY rposition DESC) rn,
    store_id as store_id
from pr.rankinglist_store s
join
location m on m.location = s.city 
where 
statkey = '2015' 
and
topcount = 3
and rn <= 3
group by 1, 2, 3, 4, 5, 6, 7, 8
order by rposition
Athanasia Ntalla
  • 285
  • 1
  • 4
  • 7
  • 1
    Does this answer your question? [Use ROW\_NUMBER() alias in WHERE](https://stackoverflow.com/questions/48956679/use-row-number-alias-in-where) – rogerdpack Oct 22 '21 at 18:01

1 Answers1

8

Aliases are not accessible, and window functions are not allowed in WHERE clause. Use a derived table:

...
select *
from (
    select 
        store_displayname as restaurant_name,
        street,
        street_no,
        zipcode,
        city, 
        topcount,
        ROW_NUMBER() OVER (PARTITION BY city
                          ORDER BY rposition DESC) rn,
        store_id as store_id
    from pr.rankinglist_store s
    join location m on m.location = s.city 
    where statkey = '2015' 
    and topcount = 3
    group by 1, 2, 3, 4, 5, 6, 7, 8
    order by rposition
    ) sub
where rn <= 3;
klin
  • 112,967
  • 15
  • 204
  • 232
  • Postgres is so silly...Note also you don't have to alias it, you can just refer to it as row_number (once its in the subquery/derived table) – rogerdpack Oct 22 '21 at 17:56
  • Postgres is open source, try to make it smarter on this point. You'll soon see why this is impossible. – klin Oct 22 '21 at 23:08