Median Function

[leigh]

Does anybody know if the MEDIAN function exists for a group by/aggregation select statement? I haven't seen it in any documentation. Just in case you haven't run across this function, it finds the middle value from a distribution. Eg. Median 1 2 6 = 2.

Thanks!

[roger]

There was no median function so far and I do not know whether there is one in 9i.
Furthermore there was no possibility to create group by function before 9i.

I wrote a median function a while ago, which looks like this:

select avg(x.sal)
from (select v.sal, v.empno, v.ename, c.cnt, decode(mod(c.cnt,2),0,2,1) rows_involved
from ( select a.sal, a.sal * -1 order_sal, a.empno, a.ename
from emp a, emp b
group by a.sal * -1, a.sal, a.empno, a.ename) v
,( select count(*) cnt
from emp c) c
where rownum <= decode(mod(c.cnt,2),0,(c.cnt+2)/2,(c.cnt+1)/2)
group by v.sal, v.empno, v.ename, c.cnt) x
where rownum <= x.rows_involved
/

Perhaps this helps...

Roger