Getting the record with latest record on join order by date

Question

Consider two tables like this

id  name 
1   john  
2   doe
3   jan

device id   id  devicename     purchasedate
1           1    iphone         2018-02-22
2           1    iphone2        2019-02-22  
3           1    iphone3        2020-02-22
4           2    iphone4        2019-02-22
5           2    iphone5        2019-02-25
6           3    iphone6        2020-03-15

So the result must be the join of the two tables and only the latest purchased date of a record must be selected. So the expected select result will be like this and if there is a record in the first table with no relationship in the second table, it should not be selected so only inner join can be used.

id name device id device name 
1  john    3        iphone3
2  doe     5        iphone5
3  jan     6        iphone6

Answer 1

You can cross apply:

select t1.name, t2.* -- or whatever columns you want
from table1 t1 cross apply
     (select top (1) t2.*
      from table2 t2
      where t2.id = t1.id
      order by t2.purchaseddate desc
     ) t2