title: Find Last Purchase Order For Each Part
body: <p>I need to find the last P.O.for parts purchased from Vendors.</p>
  <p>I was trying to come up with a way to do this using a query I found that allowed me to find
  the max Creation date for a group of Quotes linked to an Opportunity:</p>
  <pre><code>SELECT 
  
  t1.[quoteid]
  ,t1.[OpportunityId]
  ,t1.[Name]
  
  FROM
  [Quote] t1
  
  WHERE 
  
  t1.[CreatedOn] = (SELECT MAX(t2.[CreatedOn])
               FROM [Quote] t2
  
               WHERE t2.[OpportunityId] = t1.[OpportunityId])
  </code></pre>
  <p>In the case of Purchase Orders, though, I have a header table and a line item table.
  So, I need to include info from both:</p>
  <pre><code>SELECT
  
  PURCHASE_ORDER.ORDER_DATE
  ,PURC_ORDER_LINE.PURC_ORDER_ID
  ,PURC_ORDER_LINE.PART_ID
  ,PURC_ORDER_LINE.UNIT_PRICE
  ,PURC_ORDER_LINE.USER_ORDER_QTY
  
  FROM
  
  PURCHASE_ORDER,
  PURC_ORDER_LINE
  
  WHERE
  
  PURCHASE_ORDER.ID=
  PURC_ORDER_LINE.PURC_ORDER_ID
  </code></pre>
  <p>If the ORDER_DATE from the header were available in the PURC_ORDER_LINE table I thought
  this could be done like so:</p>
  <pre><code>SELECT
  
  PURC_ORDER_LINE.ORDER_DATE
  ,PURC_ORDER_LINE.PURC_ORDER_ID
  ,PURC_ORDER_LINE.PART_ID
  ,PURC_ORDER_LINE.UNIT_PRICE
  ,PURC_ORDER_LINE.USER_ORDER_QTY
  
  FROM
  
  PURC_ORDER_LINE T1
  
  WHERE T1.ORDER_DATE=(SELECT MAX(T2.ORDER_DATE)
          FROM PURC_ORDER_LINE T2
          WHERE T2.PURC_ORDER_ID=T1.PURC_ORDER_ID)
  </code></pre>
  <p><strong>But I'm not sure that's correct and, in any case, there are 2 things:</strong></p>
  <ol>
  <li>The ORDER_DATE is in the Header table, not in the line table</li>
  <li>I need the last P.O. created for each of the Parts (PART_ID)</li>
  </ol>
  <p>So:</p>
  <p>PART_A and PART_B, as an example, may appear on several P.O.s</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Part</th>
  <th>Order Date</th>
  <th>P.O. #</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>PART_A</td>
  <td>2020-08-17</td>
  <td>PO12345</td>
  </tr>
  <tr>
  <td>PART_A</td>
  <td>2020-11-21</td>
  <td>PO23456</td>
  </tr>
  <tr>
  <td>PART_A</td>
  <td>2021-07-08</td>
  <td>PO29986</td>
  </tr>
  <tr>
  <td>PART_B</td>
  <td>2019-11-30</td>
  <td>PO00861</td>
  </tr>
  <tr>
  <td>PART_B</td>
  <td>2021-08-30</td>
  <td>PO30001</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The result set would be (including the other fields from above):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ORDER_DATE</th>
  <th>PURC_ORDER_ID</th>
  <th>PART_ID</th>
  <th>UNIT_PRICE</th>
  <th>ORDER_QTY</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-07-08</td>
  <td>PO29986</td>
  <td>PART_A</td>
  <td>321.00</td>
  <td>12</td>
  </tr>
  <tr>
  <td>2021-08-30</td>
  <td>PO30001</td>
  <td>PART_B</td>
  <td>426.30</td>
  <td>8</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need a query that will give me such a result set.</p>