How to get all latest comments for all users?

Question

SQL

Let's say there are two tables, users and comments, in comments, among others, there are columns user_id and created_ts (YYYY:mm:dd H:i:s).
How can I get for all users all the most recent comments of each of them, moreover, with the condition that joins between the users and comments tables are not available?

Answer 1

Well, if the joins are not available only because the tables are on different servers, and you (Judging by your version of the "Crutch", the representation of the user in the form of "user_id" suits you. I think this option will be prettier ... (But this is not accurate ).

Select
CommentTable.user_id,
CommentTable.comment,
CommentTable.created_ts
From 
comments as CommentTable
Inner join 
(Select 
t.user_id as user_id ,
Max(t.created_ts) as LastTime
From comments as t
Group by t.user_id) as LastTimeTable
On CommentTable.user_id = LastTimeTable.user_id 
and CommentTable.created_ts = LastTimeTable.LastTime

If you have a select available to the "Users" table ... I think you can first try to create a temporary table on the server of the Comments Table
Something like

Create table #tt1
(user_id nvarchar(10),
user_name nvarchar(100))

Insert into #tt1 
Select user_id , user_name from users

and then join the temporary table of users with the table that was obtained from the query above ... then the data of the users table will also be available. (I’m not sure about optimality, but I think it will work)
The total will be like:

Create table #tt1
(user_id nvarchar(10),
user_name nvarchar(100))

Insert into #tt1 
Select user_id , user_name from users

Select
CommentTable.user_id,
users.user_name,
CommentTable.comment,
CommentTable.created_ts
From 
comments as CommentTable
Inner join 
(Select 
t.user_id as user_id ,
Max(t.created_ts) as LastTime
From comments as t
Group by t.user_id) as LastTimeTable
On CommentTable.user_id = LastTimeTable.user_id 
and CommentTable.created_ts = LastTimeTable.LastTime
Left join #tt1 as users
on CommentTable.user_id = users.user_id

drop table #tt1