Error while using Window Clause in Oracle Query

Question

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
       );

Answer 1

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