SQL MAX statement

Question

I'm going to do a better job describing my issue and I want to include my data.

I have data I need out of 6 tables:

Students s StoredGrades sg Test t TestScores ts StudentTest st StudentTestScore sts

Here's what I'm trying do. I need a report that I can run that will show me what grades a student recently received this last Semester and their best PSAT test score from their HS career.

Students (s)

ID  Student_Number  lastfirst   grade_level
1   1   Bird, Big   9
2   2   Bob, Sponge 10
3   3   Man, He 9
4   4   Rah, She    10
5   5   Myers, Michael  11

StoredGrades sg (I need to collect the Course Name and Number as well as the teacher name and the grade they received)

course_name Course_number   Grade   Teacher_name    StudentID
Foundations of Catholic Worldview Honors    THE409  A+  Mr. M   1
US Government Honors    SOC405  A   Mrs. H  1
US Government Honors    SOC405  B-  Mr. H   1
Social Justice  THE404  A   Mrs. C  2
Psychology Honors   SOC605  A-  Mrs. E  2
Forensics   SCI334  A   Mrs. R  2
Social Justice  THE404  A+  Mr. H   3
US Government Honors    SOC405  B   Mrs. C  3
Peace and Justice I Honors  THE407  A+  Mrs. E  3
Peace and Justice I THE406  A-  Mrs. R  4
Peace and Justice I THE406  A+  Mrs. E  4
Economics Honors    SOC415  A-  Mrs. R  4
Peace and Justice I THE406  A-  Mr. H   4
US Government   SOC400  A+  Mr. L   5
Peace and Justice I Honors  THE407  A+  Mr. B   5
Social Justice Honors   THE405  A+  Mrs. C  5

Test Scores come from 4 tables:

Test t (gives us the name of the table)

ID  Name
3   PSAT

TestScores ts (gives us what subscores are needed from the test)

ID  Name    TestID
51  PSAT_Critical Reading   3
53  PSAT_Math   3

StudentTest st (tells us what test the students takes, the date of that test, and how many times the student takes the test)

ID StudentID TestID Test_Date 658 1 3 12/1/2015 998 2 3 12/1/2015 100 2 3 12/1/2016 1354 3 3 12/1/2014 1500 3 3 12/1/2015 1688 3 3 12/1/2016 8875 4 3 12/1/2015 3364 5 3 12/1/2015

StudentTestScore sts (tells us how the student scores on a test. For example, Big Bird got a 78 percent on his Critical Reading PSAT test.)

ID  NumScore    StudentID   StudentTestID   TestScoreID
573 78  1   658 51
574 65  1   658 53
385 62  2   998 51
387 68  2   998 53
889 89  2   100 51
892 92  2   100 53
112 58  3   1354    51
113 59  3   1354    53
6544    68  3   1688    51
6546    62  3   1688    53
8882    75  3   1500    51
8889    79  3   1500    53
425 98  4   8875    51
426 97  4   8875    53
658 82  5   3364    51
659 86  5   3364    53

Here's what I'm wanting:

ID  Student_Number  lastfirst   grade_level Teacher_name    course_name Course_number   Grade   Test Date   Test Name   Best PSAT Reading Score Best PSAT Math Score
1   1   Bird, Big   9   Mr. M   Foundations of Catholic Worldview Honors    THE409  A+  12/1/2015   PSAT    78  65
1   1   Bird, Big   9   Mrs. H  US Government Honors    SOC405  A   12/1/2015   PSAT    78  65
1   1   Bird, Big   9   Mr. H   US Government Honors    SOC405  B-  12/1/2015   PSAT    78  65
2   2   Bob, Sponge 10  Mrs. C  Social Justice  THE404  A   12/1/2016   PSAT    89  92
2   2   Bob, Sponge 10  Mrs. E  Psychology Honors   SOC605  A-  12/1/2016   PSAT    89  92
2   2   Bob, Sponge 10  Mrs. R  Forensics   SCI334  A   12/1/2016   PSAT    89  92
3   3   Man, He 9   Mr. H   Social Justice  THE404  A+  12/1/2015   PSAT    75  79
3   3   Man, He 9   Mrs. C  US Government Honors    SOC405  B   12/1/2015   PSAT    75  79
3   3   Man, He 9   Mrs. E  Peace and Justice I Honors  THE407  A+  12/1/2015   PSAT    75  79
4   4   Rah, She    10  Mrs. R  Economics Honors    SOC415  A-  12/1/2015   PSAT    98  97
4   4   Rah, She    10  Mr. H   Peace and Justice I THE406  A-  12/1/2015   PSAT    98  97
5   5   Myers, Michael  11  Mr. L   US Government   SOC400  A+  12/1/2015   PSAT    82  86
5   5   Myers, Michael  11  Mr. B   Peace and Justice I Honors  THE407  A+  12/1/2015   PSAT    82  86
5   5   Myers, Michael  11  Mrs. C  Social Justice Honors   THE405  A+  12/1/2015   PSAT    82  86

What I'm getting is this:

ID  Student_Number  lastfirst   grade_level Teacher_name    course_name Course_number   Grade   Test Date   Test Name   Best PSAT Reading Score Best PSAT Math Score
4   4   Rah, She    10  Mrs. R  Economics Honors    SOC415  A-  12/1/2015   PSAT    98  97
4   4   Rah, She    10  Mr. H   Peace and Justice I THE406  A-  12/1/2015   PSAT    98  97

It's pulling student ID 4 as they have the very best test results. I need everyone's personal best test results.

I appreciate any help you can give me!

Answer 1

You haven't provided any sample data so the following is inference.

Your query returns a lot of extraneous columns, like teacher's name and course which means you get multiple rows for each student.

Your query appears to include joins on parameterised Student IDs, which is probably why you're getting rows for only one Student.

This query uses an analytic RANK() function to identify which score is the highest for each student and sub-test.

SELECT StuNumber
        , StuName
        , GradeLevel
        , test_name
        , MAX(CASE WHEN test_subtest ='PSAT_CriticalReading' THEN Num_Score ELSE null END) as ReadingTest
        , MAX(CASE WHEN test_subtest ='PSAT_Math' THEN Num_Score ELSE null END) as MathematicsTest 
FROM ( 
    SELECT s.Student_Number as StuNumber
           , s.lastfirst as StuName
           , s.Grade_Level as GradeLevel
            , t.name as Test_Name
            , ts.Name as Test_Subtest
            , sts.NumScore as Num_Score  
            , rank() over (partition by s.Student_Number, ts.Name order by sts.NumScore ) as score_rank
    FROM    Students s
    INNER JOIN StudentTest st ON s.ID = st.studentID  
    INNER JOIN StudentTestScore sts ON sts.StudentTestID = st.ID          
    INNER JOIN Test t ON st.TestID = t.ID  
    INNER JOIN TestScore ts ON ts.ID = sts.TestScoreID
    WHERE t.name = 'PSAT' 
    AND s.Enroll_Status=0 
    AND s.Grade_Level >=9 
    AND s.schoolid = ~(curschoolid) 
    ) TST 
AND score_rank = 1
GROUP BY StuNumber
          , StuName
          , GradeLevel
          , test_name

Filtering the sub-query on rank = 1 prevents the other scores being included. Excluding the teacher names and other stuff should give you one row per student. "Should" because in the absence of sample data I can't test this. Removing the filters on studentId means all the students are included.

If you have an actual requirement to return the wider result set shown in your question you will need to satisfy it in a different fashion.

---

By the way, this MAX(TO_CHAR(test_date,'MM/DD/YYYY')) doesn't do what you think it does. Casting the date to a string means that sorting uses ASCII values so 12/31/2013 is higher than 01/01/2016. We can do arithmetic on Oracle dates: this gives the expected result: MAX(test_date) .