Jack Jack - 1 month ago 7
MySQL Question

MYSQL Query - get the latest post by returned user

I am trying to get the latest post of each user that gets returned in this query:

select *
from users u1
where u1.id<>145 --not me
and not exists (select 1
from activity a
where a.id=145 --users I follow
and a.IdOtherUser=u1.id
and a.type='follow')


I store the posts in a table named: "Posts" and each row has a collumn "id" which is equal to the user's id, and the collumn I need to get is "path" obviously the most latest one posted first so order by the "date" collumn.

Thanks to anyone that can help, need any more info form my side just leave a comment.

Answer

This is just a direct join of your query with the posts table:

select p.id, p.path, date
from users u1, posts p
where 
p.id = u1.id
and u1.id<>145  --not me
and not exists (select 1
                from activity a
                where a.id=145 --users I follow
                    and a.IdOtherUser=u1.id
                    and a.type='follow')
order by date desc

Edit: To get only only the latest comment of each user: The self left join has one record with null on the right side. That record has the latest record on left side.

select p.id, p.path, date
from users u1, 
(select p1.* from posts p1 left join posts p2 on p1.id = p2.id and p1.date < p2.date where p2.id is null) p
where 
p.id = u1.id
and u1.id<>145  --not me
and not exists (select 1
                from activity a
                where a.id=145 --users I follow
                    and a.IdOtherUser=u1.id
                    and a.type='follow')
order by date desc
Comments