title: Using a filter condition on a 'PARTITION BY'
body: <p>I have two tables. One is a <code>Reference</code> table, used to sort priority, and one is a <code>Customer</code> table. The <code>Reference</code> table is used to give priority to each column in the <code>Customer</code> table, to give a different order for the individual columns for a single customer. </p>
  
  <p><strong>Reference Table</strong>:</p>
  
  <pre><code>---------------------------------------
  | Priority |   Attribute |  sourceID  |
  ---------------------------------------
  |   1      |     EMAIL   |      1     |
  |   2      |     EMAIL   |      2     |
  |   3      |     EMAIL   |      3     |
  |   2      |     NAME    |      1     |
  |   1      |     NAME    |      2     |
  |   3      |     NAME    |      3     |
  ---------------------------------------
  </code></pre>
  
  <p><strong>Customer table</strong>:</p>
  
  <pre><code>-----------------------------------------------------------------------
  | CustomerID |  Name   |       Email        |  SourceID |     Date    |
  -----------------------------------------------------------------------
  |    1       |  John   |       NULL         |     1     |  03/01/2017 |
  |    1       |  NULL   |   John@email.com   |     3     |  01/01/2017 |
  |    1       |   J     |  J.Smith@email.com |     2     |  02/01/2017 |
  -----------------------------------------------------------------------
  </code></pre>
  
  <p><strong>Result</strong>:</p>
  
  <pre><code>---------------------------------------------
  | CustomerID   |  Name  |       Email       |
  ---------------------------------------------
  |      1       |  John  | J.Smith@email.com |
  ---------------------------------------------
  </code></pre>
  
  <p>At the moment I'm using the following query to do this:</p>
  
  <pre><code>SELECT DISTINCT
         FIRST_VALUE(c.Name IGNORE NULLS) 
             OVER (PARTITION BY p.customerID 
                   ORDER BY r.PRIORITY, c.DATE 
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EMAIL,
        FIRST_VALUE(c.Email IGNORE NULLS) 
             OVER (PARTITION BY c.customerID 
                   ORDER BY r.PRIORITY, c.DATE 
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EMAIL
  FROM Customer c
    JOIN reference r ON c.sourceID = r.sourceID;
  </code></pre>
  
  <p>However, this does take the different attributes for each column into consideration. I need to add a filter of some sort to each of the partitions by parts.</p>
  
  <p>Can anyone assist with how I can go about doing this?</p>