Select latest row for each group from oracle

You can use analytic functions

SELECT *  FROM (SELECT c.*,               rank() over (partition by user_id order by ts desc) rnk          FROM comments c) WHERE rnk = 1

Depending on how you want to handle ties (if there can be two rows with the same user_id and ts), you may want to use the row_number or dense_rank function rather than rank. rank would allow multiple rows to be first if there was a tie. row_number would arbitrarily return one row if there was a tie. dense_rank would behave like rank for the rows that tied for first but would consider the next row to be second rather than third assuming two rows tie for first.

You can build on your query using a JOIN:

select c.*from comments c join     (select user_id, max(ts) as maxts      from comments c2      group by user_id     ) cc     on c.user_id = cc.user_id and c.ts = cc.maxts;

There are other ways. Typical advice is to use row_number():

select t.*from (select c.*, row_number() over (partition by user_id order by ts desc) as seqnum      from comments c     ) cwhere seqnum = 1;

These two queries are subtly different. The first will return duplicates if the most recent comment for a user had exactly the same ts. The second returns one row per user.

This type of problems has a very simple and very efficient solution with the dense rank first/last function:

select id,       max(user_id) keep (dense_rank last order by ts) over (partition by id) as user_id,       max(title)   keep (dense_rank last order by ts) over (partition by id) as title,       max(comment) keep (dense_rank last order by ts) over (partition by id) as comment,       max(ts)                                                                as tsfrom   comments;