CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11ggreatest-n-per-group
Z.V
Z.V

Reputation: 1461

Retrieving the latest record from a Table (Oracle SQLPlus)

I've got this table:
Thread(ThreadID, ThreadType, PostDate, PostTime, Topic, Detail, UserID)
Primary key = ThreadID, Foreign Key = UserID
I want to retrieved the latest Post together with their post detail from Every User, and I have try the following statements 

 --Without Posting details  
SELECT UserID, MAX(PostTime) AS lastPost  
FROM Thread  
GROUP BY UserID  
ORDER BY UserID  

--With Posting details  
SELECT UserID, ThreadID, MAX(PostTime) AS lastPost  
FROM Thread   
GROUP BY UserID, ThreadID  
ORDER BY UserID

But it seems that result return is different, the first one should be the correct one since it return latest post by each user (UserID in the result not repeated), but the 2nd one return every post from each user (repeated UserID).
I want to know why because I want to retrieved each user's latest post together with their post details

Upvotes: 0

Views: 1523

Answers (2)

sergdenisov
sergdenisov

Reputation: 8572

Try this:

    SELECT T1.UserID, T1.ThreadID, T1.PostTime AS LastPost  
    FROM Thread T1
    WHERE T1.PostTime = 
    (
    SELECT MAX(T2.PostTime)
    FROM Thread T2
    WHERE T2.UserID = T1.UserID
          AND T2.ThreadID = T1.ThreadID
    )

Upvotes: 0

user330315
user330315

Reputation: 

select *
from (
  SELECT UserID, 
         ThreadId, 
         PostTime,
         ThreadType,
         Topic, 
         Detail,
         row_number() over (partition by userid order by posttime desc) as rn
  FROM Thread  
) t
where rn = 1
order by userid;

Upvotes: 3

Related Questions