Oracle Analytic functions - How to reuse a PARTITION BY clause?
You can use subquery factoring, also known as the with-clause:
(untested)
with t as
( select col1
, col2
, col3
, col4
, col5
, col6
, case col7
when 'foo' then
substr(col7,1,5)
else
col7
end col7
from my_table
)
select col1
, max(col2) over (partition by col3,col4,col5,col6,col7 order by col5,col6 desc)
, min(col2) over (partition by col3,col4,col5,col6,col7 order by col5,col6 desc)
from t
Regards,
Rob.
If you are referring to the standard WINDOW clause like this:
SELECT col1,
MAX(col2) OVER(w),
MIN(col2) OVER(w)
FROM my_table
WINDOW w AS (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);
then I believe the answer is no, Oracle does not support this (checked with 11gR2).