title: SQL: use rank() together with aggregation function
body: <p>Suppose I have a data table <code>data_table</code> containing an identifier <code>ID</code> and two values, <code>val1</code> and <code>val2</code>:</p>
  <pre><code>ID       val1      val2 
  ------------------------
  1          3         4
  2          2         5
  3          8         9
  4          1        -1
  5          4        -2
  6          5         3
  7          6         6 
  8          4         1 
  9          2         9
  10         4         1
  ------------------------
  </code></pre>
  <p>I want to multiply <code>val1</code> and <code>val2</code> and calculate the average of the two lowest multiplication values:</p>
  <p><code>avg(val1*val2 | rank(val1*val2) &lt;= 2) </code> (so that it is 100% correct, we assume that every multiplication gives an unique value)</p>
  <p>I tried this:</p>
  <pre><code>Select avg(multipl)
  FROM
  (Select val1 * val2 as multipl 
  from data_table
  where RANK() OVER (ORDER BY multipl  ASC) &lt;= 2 )
  </code></pre>
  <p>Unfortunately, Oracle-SQL gives me the error message: <em>window functions are not allowed here.</em></p>
  <p>I don't know what this error message means and so I neither don't know whether the code would otherwise work out (and be efficient). Does anyone have a clue how to handle this error message or how to write the code efficiently? I would appreciate any help. Many thanks in advance.</p>