CODEWITHSUNDEEP
sqlms-accessjoingroup-bymultiple-tables
Stica
Stica

Reputation: 21

For each row in a table select the most recent date in another table joined by a value

I have the 2 following tables:

          Table1                                              Table2        
ID      IP         Timestamp                   IP             Login           User
1      10.2.4.8    02/04/2016 9.10             10.2.4.8   01/04/2016 8.20     Green   
2      10.2.4.8    02/04/2016 13.50            10.2.4.8   01/04/2016 8.50     Blue 
3      10.20.3.5   02/04/2016 13.59            10.2.4.8   02/04/2016 9.20     Red     
4      10.2.4.8    03/04/2016 10:25            10.20.3.5  04/04/2016 11:25    Blue    
5      10.20.3.5   04/04/2016 11:25            10.20.3.5  01/04/2016 10:25    Yellow

I want to match the timestamp in Table1 with the closest previous login from Table2 where the IP are the same, in order to get the user who did the login. So the result should be like this:

ID      IP         Timestamp            User
1      10.2.4.8    02/04/2016 9.10      Blue
2      10.2.4.8    02/04/2016 13.50     Red 
3      10.20.3.5   02/04/2016 13.59     Yellow     
4      10.2.4.8    03/04/2016 10:25     Red    
5      10.20.3.5   04/04/2016 11:25     Blue

I'm using SQL.Thanks for any help

The solution I came up with is this:

SELECT T1.ID, T1.IP, T2.user, MAX (T2.Login) AS Ultimo_Timestamp, T1.timestamp FROM Table2 T2, Table1 T1
WHERE T2.IP = T1.IP
AND T2.Login < T1.timestamp
GROUP BY T1.ID, T1.IP, T2.user, T1.timestamp
ORDER BY T1.ID;

I am not sure if it is correct, and if there are any better solution. Thanks

Upvotes: 0

Views: 57

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Here's one option using a correlated subquery with top:

select t.id, t.ip, t.timestamp, 
   (select top 1 t2.user
   from table2 as t2
   where t.ip = t2.ip 
     and t2.timestamp < t.timestamp  
   order by t2.timestamp desc) as user
from table1 as t

Upvotes: 1

Related Questions