Re: Median function in SQL

[Quoted] [Quoted] this was fun. one solution is like this:

select ranked.company, ranked.revenue

from 	(select a.company, a.revenue, count(*) ranking
	from companys a, companys b
	where a.revenue>b.revenue
	  or (a.revenue=b.revenue and a.rowid>=a.rowid)) ranked,
	(select round(count(*)/2) median_point
	from companys) mp

the first in-line view is a general way to do rankings - what we all intuitively think "rownum" should do, but doesn't. once the table is ranked, it's simple for the second view to locate what the median record number is.

[Quoted] this is obviously table/field specific, but we should be able to use the [Quoted] technique with the dynamic SQL packages to give us a general function something like :

"MEDIAN(table_name, field_name)"

share and enjoy.

Kevin Merritt <kevin_merritt_at_genevaco.com> wrote in article <01bd4482$5787d980$c1015c91_at_KMERRITT.GENEVACO.COM>...
> Does anyone have any idea how to implement a MEDIAN function in Oracle?
 For
> example, I would like to perform the following SQL SELECT statement:
>
> SELECT MEDIAN(revenue) FROM company_financials WHERE revenue > 1000000
>
> I can write a stored procedure for each table/column I want to perform
 this
> for, but I would like to somehow extend the built-in environment to have
> the ultimate flexiblity.
>
> Thanks in advance
>
  Received on Mon Mar 09 1998 - 00:00:00 CET