Clever Geek Handbook

PostgreSQL get latest rows / events for all users

Working on PostgreSQL 8.x (AWS Redshift)

I have a db structure like this:

userId: varchar, tstamp: datetime, event: string

So let's say I have the following lines

u2, t1, e1

u1, t2, e2

u2, t3, e1

u1, t4, e2

If u1 and u2 are user IDs, t [1..4] are timestamps, where t1> t2> t3> t4 and e1 and e2 are event types.

So how do I get the latest events run by all users. Thus, the query result will look like this:

u2, t3, e1

u1, t4, e2

Tried to figure it out using: https://en.wikipedia.org/wiki/Correlated_subquery and PostgreSQL Selecting the most recent record for a given id

But I think it's a slow brain. Failed to get.

+3


source to share


2 answers


You can do it with Postgres DISTINCT ON

:

select distinct on(userId) userId, tstamp, event
from events
order by userId, tstamp desc;


For Redshift, you can use this option from one of my previous answers :

select userId, tstamp, event from (
  select userId, tstamp, event, 
  row_number() over (partition by userId order by tstamp desc) as rownumber 
  from events
) foo
where rownumber = 1
+7


source 


select t1.userid,
       t1.date,
       t1.event
from table t1
where t1.date= (select max(t2.date) 
                  from table t2
                  where t2.userid = t1.userid);


+1


source






More articles:


All Articles