I'm trying to use window functions to get the most recent n records, following from here:
I have:
select
id,
blah,
row_number () over (
partition by blah, my_id
order by datetime) rn,
theme
from documents
where theme = 'cats';
And I get:
id | blah | rn | theme
----+-----+----+-------
1 | 1 | 1 | cats
2 | 1 | 2 | cats
3 | 1 | 3 | cats
4 | 1 | 4 | cats
5 | 1 | 5 | cats
9 | 2 | 1 | cats
8 | 2 | 2 | cats
11 | 3 | 1 | cats
12 | 4 | 1 | cats
13 | 5 | 1 | cats
14 | 6 | 1 | cats
(11 rows)
Which is great. But I want not more than 2 rows, rn <= 2
, for example. I imagine this as something like:
select
id,
blah,
row_number () over (
partition by blah, my_id
order by datetime) rn,
theme
from documents
where theme = 'cats' and
rn <= 2;
but I get:
ERROR: column "rn" does not exist
LINE 15: rn <= 1;
^
I know I can make this a subquery like the linked question, but there must be syntax I'm missing for putting the row_number in the where clause, right? What is it?
You need a derived table:
select id, blah, them
from (
select id,
blah,
row_number () over (partition by blah, my_id order by datetime) rn,
theme
from documents
) x
where theme = 'cats'
and rn <= 2;
This is basically syntactic sugar and does not cause a performance overhead.