median problems

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!! Received on Fri Oct 26 2007 - 11:35:56 CDT