title: How to retrieve the value which corresponding to the max in an other column in SQL?
body: <p>I have the following table, which represents valuations of items. </p>
  
  <pre><code>ITEM             REFERENCEDATE     VALUATION
  ------------------------------------------------
  A                25/01/2012        25.35
  A                26/01/2012        51.35
  B                25/01/2012        25.00
  </code></pre>
  
  <p>Edit: (ITEM, REFERENCEDATE) is a unique index.</p>
  
  <p>The goal is to get the latest valuations for a set of item. Which means i'm trying to create a SQL request that would return something like</p>
  
  <pre><code>ITEM             REFERENCEDATE     VALUATION
  ------------------------------------------------
  A                26/01/2012        51.35
  B                25/01/2012        25.00
  </code></pre>
  
  <p>Flowing a tutorial on GROUP BY, I ended up trying</p>
  
  <pre><code>SELECT A.ITEM, A.VALUATION, MAX(A.REFERENCEDATE)
  FROM VALUATIONS A
  GROUP BY A.ITEM
  </code></pre>
  
  <p>Full of hope that the SQL server would understand that I need A.VALUATION for the line which realizes the max for A.REFERENCEDATE for the ITEM represented on the current result line.</p>
  
  <p>But instead, I have this unpleasant error message:</p>
  
  <pre><code>Column 'VALUATIONS.VALUATION' is invalid in the select list because it is not contained 
  in either an aggregate function or the GROUP BY clause.
  </code></pre>
  
  <p>How can I indicate that the VALUATION where the maximum of REFERENCEDATE is reached should be used ?</p>
  
  <p>Note: I need a solution that works at least on Oracle and SQL Server</p>
  
  <p><strong>EDIT:</strong> Thanks everybody for your help. I was stuck in a hole try to get away with only one single SELECT ... GROUP BY request. Now I see there are two approaches that articulate around the same idea:</p>
  
  <ol>
  <li>Making a JOIN with the result of an other independant request that will return all the item/max(date) couples</li>
  <li>Using a subrequest result in the where clause which will have a different value for each item.</li>
  </ol>
  
  <p>Could anybody provide a reason (or a pointer to a reason) to prefer one to the other ?</p>