Hi Rafal
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)
putting it into a with query
(https://www.postgresql.org/docs/15/sql-select.html), in following with
queries I would select 2.1 to 2.3 with each a constant column with each
a different value you later sort by. In a next with query you can select
all the rest (except all
https://www.postgresql.org/docs/15/sql-select.html#SQL-EXCEPT) the
results of 2.1 to 2.3 for 2.4 also with the notorious sort column. In a
last with query you can put together the partial results for 2.1 to 2.4
with a union all
(https://www.postgresql.org/docs/15/sql-select.html#SQL-UNION) and
selecting sort by the sort column and the timestamp in the final select.
I do not know your background, however, sql is about data sets end it is
not always easy to get ones head around thinking in sets. I hope you
could follow my suggestions. It might not be the most efficient way but
should work.
Kind regards
Thiemo
Am 05.11.22 um 16:10 schrieb Rafal Pietrak:
Hi Everybody,
I was wondering if anybody here could help me cook up a query:
1. against a list of events (like an activity log in the database).
The list is a single table: create table events (tm timestamp, user
int, description text).
2. of which the output would be sorted in such a way, that:
2.1 most recent event would "select" most recent events of that same
user, and displayed in a group (of say 10) of them (in "tm" order).
2.2 going through the events back in time, first event of ANOTHER user
selects next group, where (say 10) most recent events of that OTHER
user is presented.
2.3 next most recent event of yet another user selects yet another
group to display and this selection process goes on, up to a maximum
of (say 20) users/groups-of-their-events.
2.4 after that, all other events are selected in tm order.
This is to present most recent telephone activities grouped by most
recent subscribers so that the dashboard doesn't get cluttered with
information but allows for an overview of other activity of most
recent users.
I tend to think, that it's a problem for a window function ... but
I've stumbled on the problem how to limit the window "frame" to just a
few (say 10) events within the "window" and have all the rest returned
as "tail" of the query.
BTW: the eventlog table is big. (and partitioned).
Any help appreciated.
-R
--
Signal (Safer than WhatsApp): +49 1578 7723737
Threema (Safer than WhatsApp): A76MKH3J
Handy: +49 1578 772 37 37
