Hi Michael,
I simplified the real query before posting it here and I now realize that I oversimplified things.
Unfortunately the real query cannot be re-written with a group by.
Some of the window functions are more complex with order by clause using complex expressions involving multiple columns.
The following example would be more realistic:
select distinct
c1,
first_value(c2) OVER (PARTITION BY c1 order by c2, c4) AS c2,
first_value(c3) OVER (PARTITION BY c1 order by coalesce(c4, '000'), c3) AS c3
from
t;
Does this give the same result and do the optimization you want?
select
c1,
min(c2) AS c2,
min(c3) AS c3,
min(c4) AS c4
from
t
group by
c1;
