CODEWITHSUNDEEP
oracle-databasewindowanalytical
Narms
Narms

Reputation: 3

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

Upvotes: 0

Views: 148

Answers (1)

MT0
MT0

Reputation: 168001

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

Upvotes: 1

Related Questions