title: Help with finding the difference (delta) from a value returned from the last two records
body: <p>I'm using MS SQL 2005 and I have created a CTE query to return values from the last two records. I then use this to find the delta of two figures returned. I have a working query of sorts but 
  I'm having problems getting anything other than the delta figure. </p>
  
  <p>here is my query:</p>
  
  <pre><code>;with data as(
      SELECT 
          NetObjectID,
          RawStatus,
          RowID,
          rn 
      from(   
          SELECT 
              CustomPollerAssignmentID AS NetObjectID,
              RawStatus,
              RowID,
              row_number() over(order by DateTime desc)as rn 
          FROM CustomPollerStatistics_Detail 
          WHERE
              (CustomPollerAssignmentID='a87f531d-4842-4bb3-9d68-7fd118004356')
      ) x where rn&lt;=2
  )
  SELECT 
      case when 
          max(case rn when 1 then RawStatus end) &gt; max(case rn when 2 then RawStatus end) 
      then 
          max(case rn when 1 then RawStatus end) - max(case rn when 2 then RawStatus end) 
      else 
          max(case rn when 2 then RawStatus end) - max(case rn when 1 then RawStatus end) 
      end as Delta
  from data having 
  (SELECT 
      case when 
          max(case rn when 1 then RawStatus end) &gt; max(case rn when 2 then RawStatus end) 
      then 
          max(case rn when 1 then RawStatus end) - max(case rn when 2 then RawStatus end) 
      else 
          max(case rn when 2 then RawStatus end) - max(case rn when 1 then RawStatus end) 
      end
  from data) &gt;= 1
  </code></pre>
  
  <p>What I'm after is to get the Delta &amp; NetObjectID returned. Each time I try, I get errors.
  <code>data.NetObjectID is invalid in the select list because it is not contained in either an aggregate function or the group by clause.</code></p>
  
  <p>If I try adding group by etc.. to the end of the query I get further error complaining about the word 'group'. </p>
  
  <p>I'm relatively new to SQL and I am picking things up as I go. Any help would be gratefully received.</p>