Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: median problems

Re: median problems

From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 26 Oct 2007 17:44:36 -0000
Message-ID: <1193420676.370280.242670@o3g2000hsb.googlegroups.com>


On Oct 26, 12:35 pm, DJH <NOS..._at_NOSPAM.COM> wrote:
> 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 have you tried?

I'll give you a BIG hint, try to write the SQL from the last requirement first.
(i.e. how do you select the scores without getting the oldest test score?)

 Post what you tried. We aren't going to write it for you, but we will help you improve what you try.

   Ed Received on Fri Oct 26 2007 - 12:44:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US