Select latest row for each group from oracle

Question

I have a table with user comments in a guestbook. Columns are: id, user_id, title, comment, timestamp.

I need to select the latest row for each user. I have tried to do this with group by but havent managed it because i cant select anything else in the same query where i group by user_id:

SELECT user_id, MAX(ts) FROM comments GROUP BY user_id

for example in this query i cant add to also select columns id, tilte and comment. How can this be done?

Answer 1

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.