How do I get the last edited post of each user in PostgreSQL?
I have user data in two tables like
1. USERID | USERPOSTID
2. USERPOSTID | USERPOST | LAST_EDIT_TIME
How do I get the last edited post and time for each user? Suppose each user has 5 posts and each of them is edited at least once.
Should I write a loop that repeats for each user, find USERPOST with MAX (LAST_EDIT_TIME) and then collect the values? I tried GROUP BY, but I can't seem to put USERPOSTID or USERPOST into the generic function. TIA.
+3
source to share
2 answers
It seems that something like this should work:
create table users(
id serial primary key,
username varchar(50)
);
create table posts(
id serial primary key,
userid integer references users(id),
post_text text,
update_date timestamp default current_timestamp
);
insert into users(username)values('Kalpit');
insert into posts(userid,post_text)values(1,'first test');
insert into posts(userid,post_text)values(1,'second test');
select *
from users u
join posts p on p.userid = u.id
where p.update_date =
( select max( update_date )
from posts
where userid = u.id )
+1
source to share
You can use window function :
select
USERID
, USERPOSTID
from
USERS
left join (
select
USERID
, row_number() over (
partition by USERID
order by LAST_EDIT_TIME desc) row_num
from
USERPOST
) most_recent
on most_recent.USERID = USERS.USERID
and row_num = 1
0
source to share