Re: Calculate median with ORACLE??

In article <856385913.31670_at_dejanews.com>, Solomon.Yakobson_at_entex.com says:

>Index is a must!!!

Order is a must not necessarily index.
Instead of this statement,
> SELECT /*+ INDEX(XYZ_I,XYZ) */
> SUM(DECODE(ROWNUM,Half_Count,DECODE(Remainder,0,X,0),
> Half_Count+1,DECODE(Remainder,0,X,2*X),
> 0
> )
> ) / 2 Median
> FROM XYZ,
> (SELECT TRUNC(COUNT(*) / 2) Half_Count,
> MOD(COUNT(*),2) Remainder,
> MIN(X) Min_Value
> FROM XYZ
> WHERE X IS NOT NULL
> )
> WHERE X >= Min_Value
> /

 Maybe you should try
> SELECT
> SUM(DECODE(ROWNUM,Half_Count,DECODE(Remainder,0,X,0),
> Half_Count+1,DECODE(Remainder,0,X,2*X),
> 0
> )
> ) / 2 Median
> FROM (select X from XYZ union select NULL from dual),
> (SELECT TRUNC(COUNT(*) / 2) Half_Count,
> MOD(COUNT(*),2) Remainder,
> MIN(X) Min_Value
> FROM XYZ
> WHERE X IS NOT NULL
> )
> WHERE X is not null
> /

I didn't try this query nor "explain" it. Received on Thu Feb 20 1997 - 00:00:00 CST