CODEWITHSUNDEEP
oracle-databasedatejoinaggregategreatest-n-per-group
SotaNice
SotaNice

Reputation: 79

aggregate function with join by most recent date Oracle

I'm trying to select a single test score for students by test type, test element, semester and ID. One score per ID. If a student has taken the test more than once, I want to only return the highest (or most recent) score for that test and element.

My problem is that there are a very small number of instances (less than 10 records out of 2000) where a student has two test scores recorded on different dates because they've re-taken the test to improve their score and we record both scores. My output therefore has a small number of records with multiple scores for unique name_ids (where test_id = 'act' and element_id = 'comp').

Examples of the two tables are:

students
----------------------------------------------------------
name_id     term_id

100         Fall
100         Spring
100         Summer
105         Fall
105         Spring
110         Fall
110         Spring
110         Summer

test_score
----------------------------------------------------------
name_id     test_id   element_id    score         test_date

100         act       comp          25            02/01/2019
100         sat       comp          1250          01/20/2019
105         act       comp          19            01/15/2019
105         act       comp          21            02/28/2019
110         act       comp          27            01/31/2019

I've tried using MAX(test_score) but perhaps could use MAX(test_date)? Either would work because the students don't report additional test scores from later dates if the scores aren't higher than what was originally reported.

This is a small part of a larger routine joining several tables so I don't know that I can replace my JOIN(s). I'm just trying to get this small subset of the routine to produce the correct number of unique records

SELECT
       a.name_id NameID,
       a.term_id TermID,
       MAX(b.score) Score

       FROM students a

       LEFT JOIN test_score b ON a.name_id = b.name_id AND b.test_id = 'act' AND b.element_id = 'comp'

     WHERE a.term_id = 'Spring'

     group by b.score,a.name_id,a.term_id
     order by a.name_id

No error messages but results from above will yield two records for NameID 105:

NameID    TermID    Score
100       Spring    25
105       Spring    19
105       Spring    21
110       Spring    27

I'm not certain how to write this to only select the highest score (or only the score from the most recent date)

Thanks for your guidance.

Upvotes: 0

Views: 32

Answers (1)

HereGoes
HereGoes

Reputation: 1320

To select the highest score the GROUP BY cannot include the score...

SELECT
       a.name_id NameID,
       a.term_id TermID,
      MAX(b.score) Score

       FROM students a

       LEFT JOIN test_score b ON a.name_id = b.name_id AND b.test_id = 'act' AND b.element_id = 'comp'

    WHERE a.term_id = 'Spring'

     group by a.name_id,a.term_id
     order by a.name_id

To get the score associated with the highest date...

SELECT x.NameID,
       x.TermID,
       y.score
FROM (
SELECT
       a.name_id NameID,
       a.term_id TermID,
   ---  MAX(b.score) Score
       MAX(b.test_date) test_date

       FROM students a

       LEFT JOIN test_score b ON a.name_id = b.name_id AND b.test_id = 'act' AND b.element_id = 'comp'

    WHERE a.term_id = 'Spring'

     group by a.name_id,a.term_id ) x

     LEFT JOIN test_score y ON x.nameid = y.name_id AND x.test_date = y.test_date
     order by x.nameid

Upvotes: 1

Related Questions