sql - Compare last to second last record for each contract -

to keep simple, question similar this question, part 2, problem is, not running oracle , can not use rownumbers.

for need more information , examples:

i have table

   contractid date          value       1          09/02/2011                1          13/02/2011     c             2          02/02/2011     d      2          08/02/2011         2          12/02/2011     c     3          22/01/2011     c     3          30/01/2011     b     3          12/02/2011     d       3          21/01/2011     

edit: added line contractid. since had code myself, display following:

   contractid date          value    value_old   1          09/02/2011                         2          08/02/2011             d   3          30/01/2011     b         c       3          30/01/2011     b          

but not want ! result should still below!

now want select last record before given date , compare previous value. suppose 'given date' 11/02/2011 in example, output should this:

   contractid date          value    value_old   1          09/02/2011                         2          08/02/2011             d   3          30/01/2011     b         c     

i have query select last record before given date. easy part. select last record before that, lost...

i hope can here, have been breaking head on days , looking answers on web , stackoverflow.

one possibility:

select a.contractid, a.date, a.value, (select top 1 b.[value]          tbl b          b.[date] < a.[date] , b.contractid=a.contractid         order b.[date] desc) old_value tbl a.date in         (select top 1 b.date          tbl b          b.contractid=a.contractid         , b.date < #2011/02/11#         order b.date desc)