title: MAX() OVER PARTITION BY in Oracle SQL
body: <p>I am trying to utilize the MAX() OVER PARTITION BY function to evaluate the most recent receipt for a specific part that my company has bought. Below is an example table of the information for a few parts from the last year:</p>
  
  <pre class="lang-none prettyprint-override"><code>| VEND_NUM | VEND_NAME    | RECEIPT_NUM | RECEIPT_ITEM | RECEIPT_DATE |
  |----------|--------------|-------------|----------|--------------|
  | 100      | SmallTech    | 2001        | 5844HAJ  | 11/22/2017   |
  | 100      | SmallTech    | 3188        | 5521LRO  | 12/31/2017   |
  | 200      | RealSolution | 5109        | 8715JUI  | 05/01/2017   |
  | 100      | SmallTech    | 3232        | 8715JUI  | 11/01/2017   |
  | 200      | RealSolution | 2101        | 4715TEN  | 01/01/2017   |
  </code></pre>
  
  <p>As you can see, the third and fourth row show two different vendors for the SAME part number.</p>
  
  <p>Here is my current query:</p>
  
  <pre><code>WITH
  
  -- various other subqueries above...
  
      AllData AS
      (
          SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE
          FROM tblVend
              INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM
          WHERE
              VEND_NUM = '100' OR VEND_NUM = '200' AND RECEIPT_DATE &gt;= '01-Jan-2017'
      ),
  
      SELECT MAX(RECEIPT_DATE) OVER PARTITION BY(RECEIPT_ITEM) AS "Recent Date", RECEIPT_ITEM
      FROM AllData
  </code></pre>
  
  <p>My return set looks like:</p>
  
  <pre class="lang-none prettyprint-override"><code>| Recent Date | RECEIPT_ITEM |
  |-------------|--------------|
  | 11/22/2017  | 5844HAJ      |
  | 12/31/2017  | 5521LRO      |
  | 11/01/2017  | 8715JUI      |
  | 11/01/2017  | 8715JUI      |
  | 01/01/2017  | 4715TEN      |
  </code></pre>
  
  <p>However, it should look like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>| Recent Date | RECEIPT_ITEM |
  |-------------|--------------|
  | 11/22/2017  | 5844HAJ      |
  | 12/31/2017  | 5521LRO      |
  | 11/01/2017  | 8715JUI      |
  | 01/01/2017  | 4715TEN      |
  </code></pre>
  
  <p>Can anybody please offer advice as to what I'm doing wrong? It looks like it is simply replacing the most recent date, not giving me just the row I want that is most recent.</p>
  
  <p>Ultimately, I would like for my table to look like this. However, I don't know how to use the MAX() or MAX() OVER PARTITION BY() functions properly to allow for this:</p>
  
  <pre class="lang-none prettyprint-override"><code>| VEND_NUM | VEND_NAME    | RECEIPT_NUM | RECEIPT_ITEM | RECEIPT_DATE |
  |----------|--------------|-------------|----------|--------------|
  | 100      | SmallTech    | 2001        | 5844HAJ  | 11/22/2017   |
  | 100      | SmallTech    | 3188        | 5521LRO  | 12/31/2017   |
  | 100      | SmallTech    | 3232        | 8715JUI  | 11/01/2017   |
  | 200      | RealSolution | 2101        | 4715TEN  | 01/01/2017   |
  </code></pre>