title: Apply aggregate function before taking where criteria
body: <p>I have two tables, TABLE_A and TABLE_B. This is what I want to do: Take all unique occurrences of FIELD_2 in TABLE_A, together with the highest occurring FIELD_1 for each of them. Then, select only those of FIELD_1 which I have there, where the corresponding FIELD_3 in TABLE_B is not 'OK'. (FIELD_1 exists in both TABLE_A and TABLE_B.) </p>
  
  <p>At first I started with a straightforward approach where I did this in three steps, just as described above. (To see what I did it is easiest to begin with Step 1, and work your way outwards, that is how I wrote the code): </p>
  
  <pre><code>/* START Step 3 – Take only those of the highest FIELD_1 from TABLE_A, where the corresponding FIELD_3 in TABLE_B is not ‘OK’ */
  SELECT DISTINCT
      FIELD_1
  FROM
      TABLE_B
  WHERE
      FIELD_1 IN 
      (
      /* START Step 2 – Take only the highest FIELD_1 from Step 1 */
      SELECT
          MAXFIELD_1
      FROM
          (
          /* START Step 1 – Take the highest FIELD_1 for all of the FIELD_2 which are in TABLE_A */
          SELECT
              FIELD_2, 
              MAX(FIELD_1) MAXFIELD_1
          FROM
              TABLE_A 
          GROUP BY
              FIELD_2
          /* END Step 1 */
          )
      /* END Step 2 */
      )
  AND
      FIELD_3 &lt;&gt; 'OK'
  /* END Step 3 */
  </code></pre>
  
  <p>However, as I continue with what I want to do this gets uglier and uglier, and I am looking for a way to shorten it by using JOIN statement. At first I tried this:</p>
  
  <pre><code>SELECT
      A.FIELD_2, 
      MAX(A.FIELD_1) FIELD_1 
  FROM
      TABLE_A A
      INNER JOIN TABLE_B B ON A.FIELD_1 = B.FIELD_1
  WHERE 
      B.FIELD_3 &lt;&gt; 'OK'
  GROUP BY
      A.FIELD_2
  </code></pre>
  
  <p>Unfortunately, what now seems to happen is that MAX(A.FIELD_1) returns the highest FIELD_1 in TABLE_A where the corresponding FIELD_3 &lt;> 'OK'. In other words, the where statement is applied first, <em>then</em> the highest FIELD_1 is selected. I would need to first select A.FIELD_2 and MAX(A.FIELD_1), and <em>after that</em>, take only those occurrences where B.FIELD_3 &lt;> 'OK'.</p>
  
  <p>How can I do this?  </p>