user1985273 user1985273 - 1 year ago 90
SQL Question

Select latest row for each group from oracle

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 Source

You can use analytic functions

               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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download