Oracle SQL 'OVER' partition error

Question

I'm trying to use this code on Oracle and it errors out. The 'partition' statement is highlighted. How do I fix this ?

select 
case when b.c_1 = 1 then b.workkey else null end workkey,
b.total_value total_value,
b.namekey namekey
from (select distinct workkey,total_value,namekey, 
      rownum() over (partition by workkey) as c_1 
      from report.arturdup
     ) b

Answer 1

The correct function is row_number():

select (case when b.c_1 = 1 then b.workkey end) as workkey,
       b.total_value as total_value,
       b.namekey as namekey
from (select distinct workkey, total_value, namekey,
             row_number() over (partition by workkey order by b.workkey) as c_1
      from report.arturdup
     ) b;

Note: You don't need a subquery for this:

select (case when row_number() over (partition by workkey order by workkey) = 1
             then b.workkey
        end) as workkey,
       b.total_value,
       b.namekey
from report.arturdup;

Notes:

• In most databases, order by is required (in some it is not, but I always use order by.
• else NULL is redundant.
• You don't need to assign a column alias that is already the name of the column.
• I always use as for column aliases.