Re: median problems

"DJH" <NOSPAM_at_NOSPAM.COM> wrote in message news:13i45rct0pcocc3_at_news.supernews.com...
> Is there a way in Oracle SQL to extract the median of the 3 tests (MATH
> READING AND WRITING) and
> their scores but only using the latest scores and ignoring older scores?
>
> I want to ignore their oldest math score at the bottom etc..
>
> select median(Score) from scores where ... etc and emp=JONES etc
>
> How can this be done? Again the three distinct grade/test CATEGORIES but
> the median of the latest 3 scores. The table will only have these three
> distinct teams but scores from different dates.
>
>
> Scores Table and data:
>
> Team TestGrade TstDate EMP
>
> MATH 15 10/11/2007 JONES
> READING 20 10/1/2007 JONES
> WRITING 30 10/9/2007 JONES
> MATH 38 9/9/2007 JONES <-ignore OLD
> WRITING 40 8/9/2006 JONES <-IGNORE OLD
>
> MATH 5 9/11/2007 SMITH
> READING 32 9/1/2007 SMITH
> WRITING 13 9/9/2007 SMITH
> READING 45 8/9/2007 SMITH <-ignore OLD
>
> Remember the last math score or reading score should not count. I just
> want the median of the latest (math, reading and writing) combined using
> the latest results and this should be found FOR A particular employee
> given to the query.
>
> so the plsql function i will be writing will be
>
> function getmedian(pvEMP) ...
>
> and returning select median()... where emp=pvEMP etc.
>
> Thanks!!

What version of Oracle ? There's a median function in Oracle 10g ... For Oracle 9i, you can use analytic functions.

Matthias Received on Fri Oct 26 2007 - 13:46:10 CDT