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