title: Oracle Analytic functions - How to reuse a PARTITION BY clause?
body: <p>I have written a complex SQL query with Oracle in which I want to use two analytic function over the same partition.</p>
  
  <p>Let's be quite simple but not too much:</p>
  
  <pre><code>SELECT col1,
         MAX(col2) OVER(PARTITION BY col3, col4, col5, col6,
                                     CASE WHEN col7 LIKE 'foo'
                                          THEN SUBSTR(col7,1,5)
                                          ELSE col7
                                     END
                                     ORDER BY col5 ASC, col6 DESC),
         MIN(col2) OVER(PARTITION BY col3, col4, col5, col6,
                                     CASE WHEN col7 LIKE 'foo'
                                          THEN SUBSTR(col7,1,5)
                                          ELSE col7
                                     END
                                     ORDER BY col5 ASC, col6 DESC)
    FROM my_table;
  </code></pre>
  
  <p>Is there a more elegant syntax for factoring the <code>PARTITION BY</code> clause?</p>
  
  <p>Thank you.</p>