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 )

      



script: http://sqlfiddle.com/#!15/4b240/4/0

+1


source


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







All Articles