Re: an difficult SQL

Great, with a little tweaking (to get the remaining rows ordered 
correctly), this did the job.

Thank you Erik.

BR

-R

W dniu 6.11.2022 o 15:23, Erik Wienhold pisze:
>> On 06/11/2022 13:48 CET Rafal Pietrak <rafal@ztk-rp.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
> 
>