How do I return only the last score a student has received by date?-CodePudding

I have been trying to figure out how to gather the Last Score a student has received on a yearly test, but the only promising way I've been able to come up with is using the MAX function. Where it all goes wrong is when I have to GROUP BY the other fields I'm returning in the result. If I could leave off the Studenttestscore_Numscore field from the GROUP BY, I think it would work, but I'm not able to do so. How can I write this to return only the last score received for every student in the database, based on the Test_Date?

The way I have it written below returns all scores that are different for each student. I believe it would work if it allowed me to remove Studenttestscore_Numscore from the GROUP BY, but it doesn't.

Right now because I have to include it to get the MAX function to work I get every score the student made on this yearly assessment unless it's the same as a previous score.

Thank you

SELECT
    ps.students.student_number,
    ps.test.name                  AS test_name,
    ps.studenttestscore.numscore  AS Studenttestscore_Numscore,
    MAX(ps.studenttest.test_date)      AS test_date
    
FROM
    ps.studenttestscore
    INNER JOIN ps.studenttest ON ps.studenttest.id = ps.studenttestscore.studenttestid
    INNER JOIN ps.students ON ps.studenttest.studentid = ps.students.id
    INNER JOIN ps.testscore ON ps.studenttestscore.testscoreid = ps.testscore.id
    INNER JOIN ps.test ON ps.test.id = ps.testscore.testid
    
WHERE
        ps.test.id = 269
    AND ps.testscore.id = 439
    
GROUP BY 
    ps.students.student_number,
    ps.test.name,
    ps.studenttestscore.numscore

CodePudding user response：

Use MAX(...) KEEP (DENSE_RANK LAST ORDER BY ...) to keep only the last values of one column and then get the maximum of another column:

SELECT s.student_number,
       MAX(t.name) AS test_name,
       MAX(sts.numscore) KEEP (DENSE_RANK LAST ORDER BY st.test_date)
         AS Studenttestscore_Numscore,
       MAX(st.test_date) AS test_date
FROM   ps.studenttestscore sts
       INNER JOIN ps.studenttest st ON st.id = sts.studenttestid
       INNER JOIN ps.students s     ON st.studentid = s.id
       INNER JOIN ps.testscore ts   ON sts.testscoreid = ts.id
       INNER JOIN ps.test t         ON t.id = ts.testid
WHERE  t.id = 269
AND    ts.id = 439
GROUP BY 
       s.student_number,
       t.id

or order the row for each partition using the ROW_NUMBER analytic function and then filter to only get the latest rows:

SELECT student_number,
       name AS test_name,
       numscore AS Studenttestscore_Numscore,
       test_date
FROM   (
  SELECT s.student_number,
         t.name,
         sts.numscore,
         st.test_date,
         ROW_NUMBER() OVER (
           PARTITION BY s.student_number, t.id
           ORDER BY st.test_date DESC
         ) AS rn
  FROM   ps.studenttestscore sts
         INNER JOIN ps.studenttest st ON st.id = sts.studenttestid
         INNER JOIN ps.students s     ON st.studentid = s.id
         INNER JOIN ps.testscore ts   ON sts.testscoreid = ts.id
         INNER JOIN ps.test t         ON t.id = ts.testid
  WHERE  t.id = 269
  AND    ts.id = 439
)
WHERE  rn = 1;

CodePudding user response：

I usually like to use row_number() for things like this. I write a subquery for the data that has multiple records that I'm trying to pull the latest of, and include a rownum that is partitioned/ordered accordingly, then join on rownum=1. No grouping is needed.

So for your query, to pull the latest record per student/test ID, it would end up being:

SELECT
    ps.students.student_number,
    ps.test.name                  AS test_name,
    ps.studenttestscore.numscore  AS Studenttestscore_Numscore,
    st.test_date
    
FROM
    ps.studenttestscore
    INNER JOIN (
        select *, rownum = row_number()over(partition by studentid, testid order by test_date desc)
        from ps.studenttest
    ) st ON st.id = ps.studenttestscore.studenttestid and st.rownum = 1
    INNER JOIN ps.students ON st.studentid = ps.students.id
    INNER JOIN ps.testscore ON ps.studenttestscore.testscoreid = ps.testscore.id
    INNER JOIN ps.test ON ps.test.id = ps.testscore.testid
    
WHERE
        ps.test.id = 269

This is assuming that the testid field is available in your ps.studenttest table since it needs to be included in the partition, but I don't know your schema so you'd have to modify accordingly if that's not the case.

CodePudding user response：

You can use returning values from DENSE_RANK() analytic function in which grouping by ps.students.student_number column is applied while descendingly sorted by ps.studenttest.test_date column in order to pick the latest matching record and remove the current GROUP BY clause such as

SELECT s.student_number,
       t.name AS test_name,
       sts.numscore AS Studenttestscore_Numscore,
       st.test_date,
       DENSE_RANK() OVER 
         (PARTITION BY s.student_number ORDER BY st.test_date DESC) AS rank_latest_first
  FROM ps.studenttestscore sts
  JOIN ps.studenttest st
    ON st.id = sts.studenttestid
  JOIN ps.students s
    ON st.studentid = s.id
  JOIN ps.testscore ts
    ON sts.testscoreid = ts.id
  JOIN ps.test t
    ON t.id = ts.testid
 WHERE t.id = 269
   AND ts.id = 439
 ORDER BY rank_latest_first
 FETCH FIRST 1 ROW WITH TIES

where WITH TIES option provides to return multiple rows whenever there are latest dates more than one for an individual student.

The version of the DB should be 12c in order to be able to use FETCH clause.