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: Steve Howard <stevedhoward_at_gmail.com>
Date: Sat, 27 Oct 2007 15:33:07 -0000
Message-ID: <1193499187.920547.16220@50g2000hsm.googlegroups.com>


On Oct 27, 11:22 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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!!
>
> As Mathias asked, what version? Try the following if on 10.x...
>
> SQL> desc tests
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> STUDENT VARCHAR2(10)
> SCORE NUMBER
> TEST_DATE DATE
>
> SQL> select * from tests;
>
> STUDENT SCORE TEST_DATE
> ---------- ---------- ---------
> tom 80 26-OCT-07
> tom 90 25-OCT-07
> tom 20 24-OCT-07
> tom 80 23-OCT-07
> lisa 60 17-OCT-07
> lisa 50 07-OCT-07
> lisa 55 27-SEP-07
> lisa 55 17-SEP-07
>
> 8 rows selected.
>
> SQL> select student,
> 2 avg(score)
> 3 from (select student,score,
> 4 row_number() over
> 5 (partition by student order by student,test_date
> desc) rn
> 6 from tests)
> 7 where rn <= 3
> 8 start with rn = 1
> 9 connect by prior rn = rn - 1 and prior student = student
> 10 group by student
> 11 order by student
> 12 /
>
> STUDENT AVG(SCORE)
> ---------- ----------
> lisa 55
> tom 63.3333333
>
> SQL> select student,
> 2 median(score)
> 3 from (select student,score,
> 4 row_number() over
> 5 (partition by student order by student,test_date
> desc) rn
> 6 from tests)
> 7 where rn <= 3
> 8 start with rn = 1
> 9 connect by prior rn = rn - 1 and prior student = student
> 10 group by student
> 11 order by student
> 12 /
>
> STUDENT MEDIAN(SCORE)
> ---------- -------------
> lisa 55
> tom 80
>
> SQL>
...BTW, you'll have to add the class, but that should get you started Received on Sat Oct 27 2007 - 10:33:07 CDT

Original text of this message

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