Re: Median function in SQL

oops - forgot the group by. didn't have a database around so i couldn't test it.

public holiday here and you should write SQL before the beer.... ;-)

but you're right, the ranking logic has a bunch of uses. (although i'm sure someone else has come up with it before.)

tzadkiel

Angelo Cavallaro <angelo.cavallaro_at_pcm.bosch.de> wrote in article <3503C953.2A3C_at_pcm.bosch.de>...
> Hi.
>
> I've tried it and it works with this group by:
>
> 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 >= b.rowid)
> group by a.rowid, a.revenue, a.company) ranked,
> (select round(count(*)/2) median_point
> from companys) mp
> where ranked.ranking=mp.median_point
>
> Great thing !!!
> The first inline-view seems to me to be a solution
> for many ordered rownum queries!
>
> Greez,
> Angelo.
>
> tzadkiel wrote:
> >
> > 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
> > where ranked.ranking=mp.median_point
> >
> > 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.
> >
> > this is obviously table/field specific, but we should be able to use
 the
> > technique with the dynamic SQL packages to give us a general function
> > something like :
> >
> > "MEDIAN(table_name, field_name)"
> >
> > share and enjoy.
> >
> --
> -----------------------------------------------------------------
> Angelo Cavallaro / / / \ TECTUM Beratungsgesellschaft
> ORACLE-SW-Berater / / / \ für Informationsverarbeitung
> ancavall.NOSPAM / / /-------\ Tel: 0711/99073-10
> @aol.com /__/__/_________\ Fax: 0711/99073-99
>
> -----------------------------------------------------------------
> Visit the Home of the Green Ribbon Campaign:
>
> http://www.ponce.oisoft.com/
>
> -----------------------------------------------------------------
>
  Received on Mon Mar 09 1998 - 00:00:00 CST