Tag: Order by with Delete

if you are using order by in sub query with delete statement like the below :

 delete from tAccountScoring where riskscoring_id=1 and id in
(select * from tAccountScoring where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 over (order by last_update desc))

You will have an error :

Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 –  “missing right parenthesis”
*Cause:   
*Action:

The workaround to solve this issue is simple like the below :

delete from tAccountScoring
where riskscoring_id=1 and id in
(select ID  from (select * from tAccountScoring
where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 order by last_update))

Thank you
Osama Mustafa