CODEWITHSUNDEEP
sqloracle-databasewindow-functions
simplycoding
simplycoding

Reputation: 2967

Unable to user an ORDER BY clause in a window function with Oracle

I've got a query that works fine without the ORDER BY clause in the window function:

select
"TABLE_NAME",
"DENSITY",
"NUM_DISTINCT",
ROWNUM,
median(DENSITY) OVER (PARTITION BY table_name )
from ALL_TAB_COLUMNS a
where 1=1
and owner = 'SYS'
and table_name='CARRY'
and "NUM_DISTINCT" < 1000
and DENSITY < 1
AND num_nulls = 0

But I definitely need this order by clause to get the data in the format I need it. If I add the order by, I get this weird error message: 

ORA-30487: ORDER BY not allowed here
30487. 00000 -  "ORDER BY not allowed here"
*Cause:    DISTINCT functions and RATIO_TO_REPORT cannot have an ORDER BY
*Action:
Error at Line: 6 Column: 47

Here's the full SQL with the order by:

select
"TABLE_NAME",
"DENSITY",
"NUM_DISTINCT",
ROWNUM,
median(DENSITY) OVER (PARTITION BY table_name ORDER BY "DENSITY")
from ALL_TAB_COLUMNS a
where 1=1
and owner = 'DEANZA'
and table_name='CARRIER_A'
and "NUM_DISTINCT" < 1000
and DENSITY < 1
AND num_nulls = 0

Upvotes: 0

Views: 1343

Answers (1)

Bla...
Bla...

Reputation: 7288

As stated in the documentation, for MEDIAN you cannot use ORDER BY in its OVER clause.

MEDIAN will take a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value once the values are sorted. So there is no need to use ORDER BY anyway.

Upvotes: 2

Related Questions