oracle-databasewindowanalytical

Error while using Window Clause in Oracle Query


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

Solution

  • 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