I have been looking at this code for the past two days now and I can not seem to get it to work. It does work without the Window clause though.
It keeps giving me:
ORA-00907: missing right parenthesis.
select P.*,
first_value(product_name) over (w) MAX_PRICE,
Last_value(product_name) over (w) MIN_PRICE
from product P
window w as (
partition by product_category
order by price desc
range between unbounded preceding and unbounded following
);
The window clause goes inside the analytic function:
select P.*,
first_value(product_name) over (
partition by product_category
order by price desc
range between unbounded preceding and unbounded following
) AS MAX_PRICE,
Last_value(product_name) over (
partition by product_category
order by price desc
range between unbounded preceding and unbounded following
) MIN_PRICE
from product p;
Or, from Oracle 21, you can use:
select P.*,
first_value(product_name) over w AS MAX_PRICE,
Last_value(product_name) over w AS MIN_PRICE
from product p
window w as (
partition by product_category
order by price desc
range between unbounded preceding and unbounded following
)
(Without the brackets around the window in the analytic function.)
db<>fiddle here