title: Selecting the record with most recent datetime value
body: <p>I have three tables</p>
  
  <p>Buildings</p>
  
  <pre><code>BuildingKey(int) | BuildingID(varchar) | ...
  </code></pre>
  
  <p>Areas</p>
  
  <pre><code>areaKey(int) | areaID(varchar) | buildingKey(int-FK[Buildings]) | ...
  </code></pre>
  
  <p>Transactions</p>
  
  <pre><code>transactionKey(int) | areaKey(int-FK[Areas]) | value(float) | trnDateTime(DateTime)
  </code></pre>
  
  <p><br/>
  There may be several <code>Areas</code> in a <code>Building</code>.<br/> </p>
  
  <p>All areas have many <code>Transactions</code>, with different <code>value</code> and different <code>trnDateTime</code>. </p>
  
  <p>What I want to do is get the latest <code>value</code> (Transaction) of <strong>each</strong> <code>Area</code> of a <code>Building</code> (when the <code>buildingKey</code> is given).<br/></p>
  
  <p>I referred to some previous questions like <a href="https://stackoverflow.com/questions/5209391/select-most-recent-record-in-sub-child">this one</a>, and tried following.</p>
  
  <p>(1)</p>
  
  <pre><code>DECLARE @buildingKey INT
  SET @buildingKey = 3
  
  ;WITH Vals AS (
      SELECT  T.areaKey AS AreaKey,
              T.value AS CurrentValue, 
              T.trnDateTime AS RecordedDateTime,                
              ROW_NUMBER() OVER (PARTITION BY B.buildingKey ORDER BY T.trnDateTime DESC) RowID
  
      FROM    Buildings B INNER JOIN
              Areas A ON  B.buildingKey = A.buildingKey INNER JOIN 
              Transactions T  ON A.areaKey = T.areaKey
  
      WHERE B.buildingKey = @buildingKey
  )
  SELECT  AreaKey,
          CurrentValue,
          MAX(RecordedDateTime) AS RecentReading,
          RowID
  FROM    Vals
  WHERE   RowID = 1
  GROUP BY AreaKey, CurrentValue, RowID
  </code></pre>
  
  <p>!) Returns the latest value (among all the Areas); <strong>not</strong> the latest value of <strong>each</strong> Area! <br/></p>
  
  <p>(2)</p>
  
  <pre><code>DECLARE @buildingKey INT
  SET @buildingKey = 3
  
  SELECT  A.areaKey AS AreaKey, 
          A.areaID AS AreaID,
          T.value AS CurrentValue,
          T.trnDateTime AS RecordedDateTime                   
  
  FROM Areas A, Buildings B, Transactions T
  
  WHERE   @buildingKey = B.buildingKey AND 
          B.buildingKey = A.buildingKey AND
          T.areaKey = A.areaKey AND
          T.trnDateTime IN (SELECT MAX(T.trnDateTime), T.areaKey
                                    FROM Transactions T
                                    GROUP BY T.areaKey)
  </code></pre>
  
  <p>!) Gives an error --> </p>
  
  <pre><code>Msg 116, Level 16, State 1, Line 16
  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
  </code></pre>