Re: Median function in SQL

This does work when n (count) is an odd number but when n is an even number shouldn't the two values in the middle (n/2 and (n/2)+1) be averaged for the median? How would this be done?

Thanks,
Greg

In article <3503C953.2A3C_at_pcm.bosch.de>,   Angelo Cavallaro <angelo.cavallaro_at_pcm.bosch.de> wrote:
>
> 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/
>
> -----------------------------------------------------------------
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Mar 17 1998 - 00:00:00 CST