title: SQL Condition on Window function
body: <p>I want to do a special request on my database (PostgreSQL v9.4.5), but I don't manage to do it.</p>
  
  <p>In order to simply, let's say I have the following table <strong>AvgTemperatures</strong>, representing different averages of temperature taken in different cities, and calculated on different length of time (counted in months) :</p>
  
  <pre><code> id |   city    |  avg | months 
  ----+-----------+------+--------
    1 |  New-York |   20 |     3   &lt;--- average temperate over the last 3 months
    2 |  New-York |   19 |     6   &lt;--- average temperate over the last 6 months
    3 |  New-York |   15 |    12   &lt;--- etc
    4 |  New-York |   15 |    24
    5 |    Boston |   13 |     3
    6 |    Boston |   18 |     8
    7 |    Boston |   17 |    12
    8 |    Boston |   16 |    15
    9 |   Chicago |   12 |     2
   10 |   Chicago |   14 |    12
   11 |     Miami |   28 |     1
   12 |     Miami |   25 |     4
   13 |     Miami |   21 |    12
   14 |     Miami |   22 |    15
   15 |     Miami |   20 |    24
  </code></pre>
  
  <p>Now, imagine that I want to select all the rows concerning the measures in a city where at least one average has been over 19 degrees. In this case I want :</p>
  
  <pre><code> id |   city    |  avg | months 
  ----+-----------+------+--------
    1 |  New-York |   20 |     3  
    2 |  New-York |   19 |     6  
    3 |  New-York |   15 |    12  
    4 |  New-York |   15 |    24  
   11 |     Miami |   28 |     1  
   12 |     Miami |   25 |     4  
   13 |     Miami |   21 |    12  
   14 |     Miami |   22 |    15  
   15 |     Miami |   20 |    24  
  </code></pre>
  
  <p>I could do something like :</p>
  
  <pre><code> SELECT *
   FROM AvgTemperatures
   WHERE MIN(avg) OVER (PARTITION BY city) &gt; 16
  </code></pre>
  
  <p>But :</p>
  
  <pre><code>********** Erreur **********
  
  ERROR: window functions not allowed in WHERE clause
  </code></pre>
  
  <p>What's more, I cannot use <code>GROUP BY</code> as in :</p>
  
  <pre><code> SELECT *
   FROM AvtTemperatures
   GROUP BY city
   HAVING MIN(avg) &gt; 16
  </code></pre>
  
  <p>because I will lose information due to the aggregation (by the way this query is not valid because of the "SELECT *").</p>
  
  <p>I'm pretty sure I can use the <code>OVER PARTITION BY</code> to solve that, but I don't know how. Does someone have an idea ?</p>