title: How to join table to itself and select max values in SQL
body: <p>I have a <code>contracts</code> table:</p>
  
  <pre><code>contractId    date       price     partId
  1             20120121   10        1
  2             20110130   9         1
  3             20130101   15        2
  4             20110101   20        2
  </code></pre>
  
  <p>The contract with greatest <code>date</code> being the active contract (don't blame me, I blame infor for creating xpps)</p>
  
  <p>I need to create query to see only active contracts  (one contract per part, the contract with highest date).</p>
  
  <p>So the result of the query should be like this:</p>
  
  <pre><code>contractId    date       price     partId
  1             20120121   10        1
  3             20130101   15        2
  </code></pre>
  
  <p>I am out of ideas here, I tried self joining the table, I tried aggregation functions, but I can't figure it out. If anyone would have any idea, please share them with me..</p>