Compare last to second last record for each contract

To keep it simple, my question is similar to THIS QUESTION, PART 2, only problem is, I am not running Oracle and thus can not use the rownumbers.

For those who need more information and examples:

I have a table

  contractId date          value    
  1          09/02/2011     A         
  1          13/02/2011     C          
  2          02/02/2011     D   
  2          08/02/2011     A  
  2          12/02/2011     C  
  3          22/01/2011     C  
  3          30/01/2011     B  
  3          12/02/2011     D    
  3          21/01/2011     A

EDIT: added another line for ContractID. Since I had some code myself, but that would display the following:

  contractId date          value    value_old
  1          09/02/2011     A                  
  2          08/02/2011     A         D
  3          30/01/2011     B         C    
  3          30/01/2011     B         A 

But that is not what I want ! The 开发者_JAVA百科result should still be as below!

Now I want to select the last record before a given date and compare that with the previous value. Suppose the 'given date' is 11/02/2011 in this example, the output should be like this:

  contractId date          value    value_old
  1          09/02/2011     A                  
  2          08/02/2011     A         D
  3          30/01/2011     B         C    

I do have the query to select the last record before the given date. That is the easy part. But to select the last record before that, I am lost...

I really hope I can get some help here, have been breaking my head over this for days and looking for answers on the web and stackoverflow.

---

One possibility:

SELECT a.contractId, a.Date, a.Value, (SELECT Top 1 b.[Value] 
        FROM tbl b 
        WHERE b.[Date] < a.[Date] And b.ContractID=a.ContractID
        ORDER BY b.[Date] Desc) AS Old_Value
FROM tbl AS a
WHERE a.Date IN 
       (SELECT TOP 1 b.Date 
        FROM tbl b 
        WHERE b.ContractID=a.ContractID
        AND b.Date < #2011/02/11#
        ORDER BY b.date DESC)

---

As promised, I would also post my answer. Although at this point, I still think Remou's answer is better, since the code is shorter and it seems more efficient (calls the same table fewer times). But here goes:

Query1:

SELECT c.contractID, c.firstofdates, a.value, d.value, d.date
FROM (table1 AS A RIGHT JOIN (SELECT b.cid,max(b.date) AS FirstOfdates
FROM table1 as B
where b.date < #02/11/2011#
GROUP BY b.contractID    )  AS c ON (a.date = c.firstofdates) AND (a.contractID =   c.contractID)) 
LEFT JOIN (select e.contractID, e.date, e.value
from table1 as e
)  AS d ON (d.date < c.firstofdates) AND (d.contractID = c.contractID);

This query actually gives the result with the extra row for the 3rd contractID.

Query2:

SELECT b.contractID, max(a.date) AS olddate
FROM table1 AS a RIGHT JOIN (select contractID, firstofdates 
from Query1)  AS b ON (a.contractID= b.contractID) AND (a.date < b.firstofdates)
GROUP BY b.contractID;

And then to combine both:

Query3:

SELECT Query1.contractID, Query1.firstofdates AS [date], Query1.A.value AS [value], Query1.d.value  AS [old value]
FROM Query1 RIGHT JOIN Query2 
ON (Query1.date=Query2.olddate or Query2.olddate is null) AND   (Query1.cid = Query2.cid);