Re: an difficult SQL

[Erik Wienhold <ewie@xxxxxxxxx>]

> On 06/11/2022 13:48 CET Rafal Pietrak <rafal@xxxxxxxxx> 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