Portable SQL with Group By and Joins -

i have following 2 tables:

table1:

id devicename devicelocation additionalcolumn1 additionalcolumn2 1  xyz        africa         somecoltext1      sometext1 2  abc        usa            somecoltext2      sometext2 

table2:

id name externalid devicename devicelocation version 1  yyy  10          xyz        africa         1  2  bbb  11          xyz        africa         1  3  uuu  10          abc        usa            2  

i'm trying come sql me values out of table2 joins table1 , fetch me additional fields table1(additionalcolumn1, additionalcolumn2). additionally, want elements table2 has maximum version. expected result should be:

id name externalid devicename devicelocation version additionalcolumn1  2  bbb  11          xyz        africa         1      somecoltext1 3  uuu  10          abc        usa            2      somecoltext2 

i have basic version setup, portable version works across databases?

i think 1 of possible solutions:

select t2.id,        t2.name,         t2.externalid,        t2.devicename,         t2.devicelocation,         t2.version,         t1.additionalcolumn1,        t1.additionalcolumn2  (select externalid, max(version) version table2 group externalid) tmp join table2 t2 on tmp.externalid = t2.externalid , tmp.version = t2.version join table1 t1 on t1.devicename = t2.devicename , t1.devicelocation = t2.devicelocation