title: SQL: Selecting multiple fields with one unique field based on most recent date
body: <p>I'm attempting to write an SQL statement to select a unique part number based on the most recent date.
  If I have just the two fields PartNo and ReceiveDate I could do:</p>
  <pre><code>&quot;SELECT PartNo, Max(ReceiveDate) FROM Table GROUP BY PartNo;&quot;
  </code></pre>
  <p>and this would return the unique PartNo and the most recent date.</p>
  <p>The problem is that I also want to include the fields VendorName and Qty (But I just want PartNo to be unique). I've tried:</p>
  <pre><code>&quot;SELECT PartNo, VendorName, Qty, Max(ReceiveDate) FROM Table GROUP BY PartNo;&quot;
  </code></pre>
  <p>and</p>
  <pre><code>&quot;SELECT PartNo, VendorName, Qty, Max(ReceiveDate) FROM Table GROUP BY PartNo, VendorName, Qty;&quot;
  </code></pre>
  <p>I understand why these two SELECT statements are wrong, the first one doesn't run since VendorName and Qty aren't in the GROUP BY clause or part of an aggregate function, and in the second one it selects a unique record based on PartNo AND VendorName AND Qty, not just PartNo.</p>
  <p>If someone could help me write the correct SQL statement that would be must appreciated. I'm using Microsoft Access which uses Jet SQL which is very similar to T-SQL.</p>