Choose the last row for each group from oracle

Question

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

I need to select the last row for each user. I tried to do this with a group, but havent controlled it because I cannot select anything else in the same query where I group user_id:

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

for example, in this query I cannot add to also select the id, tilte and comment columns. How can I do that?

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

, ( user_id ts), row_number dense_rank, rank. rank , . row_number , . dense_rank rank , , , , , .

Answer 2

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;

. row_number():

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

. , ts. .

Answer 3

This type of problem has a very simple and very effective solution using the function dense rank first/last:

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 ts
from   comments;