title: DB2 restrict analytic function to subselect
body: <p>I often encountered the situation where I wanted to restrict the action of an analytic function (or OLAP in DB2) to a certain subset of the present data. Here is an example:</p>
  
  <pre><code>WITH MY_TABLE AS
  (
       SELECT 1 AS FIELD1, 'A' AS FIELD2,  1 AS FIELD3, 'X' AS FIELD4 FROM DUAL
       UNION
       SELECT 1 AS FIELD1, 'A' AS FIELD2, 2 AS FIELD3, 'Y' AS FIELD4 FROM DUAL
       UNION
       SELECT 1 AS FIELD1, 'B' AS FIELD2, 3 AS FIELD3, 'X' AS FIELD4 FROM DUAL
       UNION
       SELECT 1 AS FIELD1, 'A' AS FIELD2, 4 AS FIELD3, 'Z' AS FIELD4 FROM DUAL
       UNION
       SELECT 1 AS FIELD1, 'B' AS FIELD2, 5 AS FIELD3, 'Z' AS FIELD4 FROM DUAL
       //...
  )
  SELECT FIRST_VALUE(FIELD4) OVER (PARTITION BY FIELD1 ORDER BY FIELD3) AS FIELD2_A
  FROM MY_TABLE
  WHERE FIELD2='A'  // need that as well for 'B', 'C' etc.
  </code></pre>
  
  <p>Now it is cumbersome to do this for all required values of <code>FIELD2</code> and afterwards doing a JOIN or a UNION, moreover since the constraints might be more complicated.</p>
  
  <p>Thus I really want to handle it in a single SELECT. Here is a trial:</p>
  
  <pre><code>//...
  SELECT CASE WHEN FIELD2 = 'A'
              THEN FIRST_VALUE(FIELD4)
                         OVER (PARTITION BY FIELD1
                               ORDER BY CASE WHEN FIELD2='A' THEN 0 ELSE 1 END, FIELD3)
              ELSE NULL END AS FIELD4_A,
         CASE WHEN FIELD2 = 'B'
              THEN FIRST_VALUE(FIELD4)
                         OVER (PARTITION BY FIELD1
                               ORDER BY CASE WHEN FIELD2='B' THEN 0 ELSE 1 END, FIELD3)
              ELSE NULL END AS FIELD4_B,
        //a bit more complicated 
        CASE WHEN FIELD2 IN ('A','C')
              THEN FIRST_VALUE(FIELD4)
                         OVER (PARTITION BY FIELD1
                               ORDER BY CASE WHEN FIELD2 IN ('A','C') THEN 0 ELSE 1 END, FIELD3)
              ELSE NULL END AS FIELD4_AC
  FROM MY_TABLE
  </code></pre>
  
  <p>The idea is to order the data set by the WHERE-criterion, but since there might be cases where rows with such a constraint do not exist in the partition (and thus the unwanted result would be obtained), a non-null value is returned only if the <code>FIELD2</code> meets the constraint (this is done in the outer <code>CASE</code> statement).</p>
  
  <p>I guess this approach works (--I haven't tested it yet), but it is a bit detoured. <strong>Are there easier or more direct approaches to accomplish the above task?</strong></p>