sql - Join tables by t1.datetime and MAX(t2.datetime) -

i have problem joining 2 tables datetime column.

i need join on table1.datetime>max(table2.datetime). don't have other column join on. can me?

example of table1 (over 370.000 rows):

timestamp                data1  data2  data3 -----------------------  -----  -----  ----- 2011-05-09 08:55:19.990  x1     w12    j3 2011-05-09 08:56:19.990  x4     w22    j3 2011-05-09 08:57:19.990  x5     w23    j3 2011-05-09 08:58:19.990  x7     w25    j3 2011-05-09 08:59:19.990  x2     w19    j3 2011-05-09 09:01:19.990  x3     w18    j3 

example of table2 (over 2.000 rows):

timestamp                data8 -----------------------  ----- 2011-05-09 07:55:11.990  y1 2011-05-09 07:56:13.990  y9 2011-05-09 08:17:14.990  y3 2011-05-09 08:28:15.990  y8 2011-05-09 08:59:16.990  y5 2011-05-09 09:02:19.990  y6 

so data in table1 joined table2 should have values:

timestamp                data1  data2  data3  timestamp                data8 -----------------------  -----  -----  -----  -----------------------  ----- 2011-05-09 08:55:19.990  x1     w12    j3     2011-05-09 08:28:15.990  y8 2011-05-09 08:56:19.990  x4     w22    j3     2011-05-09 08:28:15.990  y8 2011-05-09 08:57:19.990  x5     w23    j3     2011-05-09 08:28:15.990  y8 2011-05-09 08:58:19.990  x7     w25    j3     2011-05-09 08:28:15.990  y8 2011-05-09 08:59:19.990  x2     w19    j3     2011-05-09 08:59:16.990  y5 2011-05-09 09:01:19.990  x3     w18    j3     2011-05-09 08:59:16.990  y5 

use option cross apply , top operators

select * dbo.table1 t1 cross apply (                                 select top 1 t2.[timestamp], t2.data8                                 dbo.table2 t2                                 t2.[timestamp] < t1.[timestamp]                                 order t2.[timestamp] desc                                 ) o 

see demo on sqlfiddle