The magical median

In the past, it has been difficult to determine the median in Oracle because the median represents the middle value in an ordered set (and of course, relational databases are all about unordered sets.

The following query can determine the median salary from the EMP table

SQL> select avg(sal) from 
  2  ( select x.sal
  3   from EMP x, EMP y
  4   group by x.sal
  5   having sum(sign(1-sign(y.sal-x.sal))) in 
  6          ( trunc((count(*)+1)/2), trunc((count(*)+2)/2) ) )

but lets face it, that is basically goobledeegook to most people. 9i gives a far more readable facility using the new PERCENTILE_DISC (discrete) and PERCENTILE_CONT (continuous) functions

SQL> select PERCENTILE_CONT(0.5)
  2    within group (ORDER BY sal DESC) sal
  3  from emp;

       SAL
----------
      1550