I'm trying to create a query but i'm having some trouble with it. I have two tables:
I'm trying to list all users and their comments, which can be done quite easily with an inner join. However, i get various comments per user, since i joined the result. I just want their latest comment. Any ideas? :)
this should do it:
select distinct on(u.name, u.id) *
from comments c, users u
where u.id=c.uid
order by u.name, u.id, c.date desc
For PostgreSQL 8.4+:
SELECT x.*
FROM (SELECT u.*, c.*,
ROW_NUMBER() OVER (PARTITION BY u.id
ORDER BY c.date DESC, c.time DESC) AS rnk
FROM USERS u
JOIN COMMENTS c ON c.uid = u.id) x
WHERE x.rnk = 1
This might work:
EDIT:
I updated the query to this:
SELECT u.id, u.name, u.email, t.id, t.uid, t.comment, t.date, t.time
FROM users u
LEFT OUTER JOIN
(
select c.id, m.uid, c.comment, m.cdate, c.time
from comments c
right outer join
(
select uid, max(date) as cdate
from comments
group by uid
) as m
ON c.cdate = m.cdate
) t
ON u.id = t.uid
Assuming comment id is autoincrement, find the maximum commentid per user (the latest comment)
SELECT u.id, u.name, u.email, c.id, c.uid, c.comment, c.date, c.time
FROM users u
JOIN comments c ON u.id = c.uid
JOIN
(
select uid, max(id) id
from comments
group by uid
) as c2 ON c.id = c2.id AND c.uid = c2.uid
OMG Ponies certainly has the best answer, but here is another way to do it, without any extended database feature:
select
u.name,
c.comment,
c.comment_date_time
from users as u
left join comments as c
on c.uid = u.id
and
c.comment_date_time -
(
select max(c2.comment_date_time)
from comments as c2
where c2.uid = u.id
) = 0
I have merge your date and time columns into comment_date_time in this example.
