Re: an difficult SQL

> On 06/11/2022 13:48 CET Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>
> W dniu 5.11.2022 o 19:05, Thiemo Kellner pisze:
> >
> > You first could select the three users with the most recent entries with
> > a windowing function
> > (https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)
>
> surely I'm missing something crucial here:
> select row_number() over w,* from eventlog where row_number() over w < 5
> window w as (partition by user);
> ERROR: window functions are not allowed in WHERE
>
> So I'm unable to pick a limited number of rows within the user
> "group-window" ranges.
>
> Without that, I cannot proceed.
>
> Any suggestions?

Windows functions are only permitted in SELECT and ORDER BY because they are
executed after WHERE, GROUP BY, and HAVING[1].

You need a derived table to filter on row_number:

with
ranked as (
select *, row_number() over w
from eventlog
window w as (partition by user)
)
select *
from ranked
where row_number < 5;

[1] https://www.postgresql.org/docs/15/tutorial-window.html

--
Erik