Re: an difficult SQL - Mailing list pgsql-general

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