Re: Calculate median with ORACLE??

Yes, using UNION will force ORACLE to order rows. But you did not take into consideration that UNION will not return duplicates. Therefore your calculation of a median value will not be right.

Solomon.Yakobson_at_entex.com

In article <5eh4dp$2qj_at_ys.ifremer.fr>,
  elhuilli_at_ifremer.fr (Eric L'Huillier, Ifremer PDG-DGD-DGO-INGE) wrote:
>
> 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.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet