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
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
ts), you may want to use the
dense_rank function rather than
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.