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(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
>
>