CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
JavascriptGOD
JavascriptGOD

Reputation: 7

Challenge in PostgreSQL query (group by and having issue)

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? :)

Upvotes: 0

Views: 242

Answers (5)

maniek
maniek

Reputation: 7307

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

Upvotes: 2

plang
plang

Reputation: 5656

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.

Upvotes: 0

niktrs
niktrs

Reputation: 10066

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

Upvotes: 0

Taryn
Taryn

Reputation: 247710

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

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

Upvotes: 1

Related Questions