Re: Puzzler: Finding median value with SQL?

In article <2mt2qaINNshc_at_dns1.nmsu.edu>, Mcrider <mcrider_at_acca.nmsu.edu> wrote:
>
>How can one find a median value in Oracle? In a statistical distribution
>the median value is the value of the variate above and below which equal
>numbers of items lie.

The following sql statement will do it. (if you have an even number of rows, the "lesser" of the two median values is returned. Change the - 1 on line 5 to + 1 to return the "greater" of the two medians)

	1 select a.val
	2 from temp a, temp b
	3 where a.val >= b.val
	4 group by a.val
	5 having count(*) - 1 = (select count(val)
	6     from temp where val >= a.val)
	7 or count(*) = (select count(val)
	8     from temp where val >= a.val)

Hope this helps

L. Scott Johnson
Senior Programmer
Research Planning, Inc.
(803) 256-7322 Received on Mon Mar 28 1994 - 16:49:43 CEST