title: SQL - Derived table joining
body: <p>I have a SQL query for a table in which the user can create multiple 'revisions' of a form. Currently we pass in the ID of the revision into the query to retrieve the values (as you can probably guess), which is fine - however I want to extend to also select the previous revisions rows (if they is a previous revision). Each revision has a number which is incremented when they create a new revision. Here's my query so far which doesn't seem to run (obviously the value 1,value 2 are the actual columns in my query)</p>
  
  <pre><code>SELECT ID, SageJobID, SageJobPK, DateID, Revision, StatusID, Value1, Value2
  FROM   CVRs
  LEFT OUTER JOIN (SELECT TOP(1) * 
                    FROM CVRs AS prevCVR 
                    WHERE (prevCVR.DateID = CVRs.DateID 
                      AND prevCVR.SageJobPK = CVRs.SageJobPK 
                      AND prevCVR.ID &lt;&gt; CVRs.ID) 
                    ORDER BY prevCVR.Revision DESC) AS 'PrevCVR'
  WHERE        (CVRs.ID = @ID)
  </code></pre>
  
  <p>It seems I can't access the main CVR row I'm selecting from my join. Any ideas?</p>