return result by latest date - oracle query

The query below currently returns the max(SATTOTAL) but I need to return the SATTOTAL associated with the latest studentstest.test_date instead.  I can't figure out how to return the value based on that criteria.  Can anyone help?  Thanks!!

select
STUDENT_NUMBER as "StuNum",
max(CMTSCIENCE) AS "Grade 8 CMT Science",
max(SATTOTAL) AS "Grade 8 CMT Science"

FROM
(

select 
students.student_number AS STUDENT_NUMBER,

CASE WHEN UPPER(Test.Name) = 'CMT' AND StudentTest.GRADE_LEVEL = '8' AND TestScore.NAME = 'Science Scale Score'
THEN StudentTestScore.numscore ELSE NULL END AS CMTSCIENCE,--"Grade 8 CMT Science"

CASE WHEN UPPER(Test.Name) = 'SAT' AND TestScore.NAME = 'Total Score'
THEN StudentTestScore.numscore ELSE NULL END AS SATTOTAL,--"Latest SAT Total"

StudentTest.TEST_DATE

from
students
LEFT JOIN StudentTestScore ON StudentTestScore.StudentID = Students.ID
LEFT JOIN StudentTest ON StudentTestScore.StudentTestID = StudentTest.ID
LEFT JOIN Test ON StudentTest.TestID = Test.ID
LEFT JOIN TestScore ON StudentTestScore.TestScoreID = TestScore.ID

WHERE
StudentTestScore.NumScore > 0
AND Students.STUDENT_NUMBER IN ('3023484','3010864')

)

WHERE
CMTSCIENCE > 0
OR SATTOTAL > 0

group by
STUDENT_NUMBER

order by
STUDENT_NUMBER

Open in new window

BasssqueAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Sorry, got pulled away.

Try this:
select studentid,
	max(case when rn=1 and score_name = 'Total Score' then numscore end) total_score,
	max(case when rn=1 and score_name = 'Math Score' then numscore end) match_score,
	max(case when rn=1 and score_name = 'EBRW Score' then numscore end) ebrw_score
from (
	select studentid, numscore, score_name, row_number() over(partition by studentid, score_name order by test_date desc) rn from tab1
)
group by studentid
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Without sample data and expected results to look at, things are a guess.

Are you just wanting the MAX(test_date)?

select
STUDENT_NUMBER as "StuNum",
max(CMTSCIENCE) AS "Grade 8 CMT Science",
max(SATTOTAL) AS "Grade 8 CMT Science",
MAX(TEST_DATE)
...
0
 
BasssqueAuthor Commented:
I tried that already but it only returned the MAX(TEST_DATE) in that column only.  I need the SATTOTAL that is associated with the MAX(TEST_DATE) but not actually return the date itself.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Maybe the ROW_NUMBER trick we've used before?

You might need to tweak the partition columns to get it working.

select
STUDENT_NUMBER as "StuNum",
max(CMTSCIENCE) AS "Grade 8 CMT Science",
max(SATTOTAL) AS "Grade 8 CMT Science"
FROM
(
select 
	students.student_number AS STUDENT_NUMBER,
	CASE WHEN UPPER(Test.Name) = 'CMT' AND StudentTest.GRADE_LEVEL = '8' AND TestScore.NAME = 'Science Scale Score'
		THEN StudentTestScore.numscore ELSE NULL END AS CMTSCIENCE,--"Grade 8 CMT Science"
	CASE WHEN UPPER(Test.Name) = 'SAT' AND TestScore.NAME = 'Total Score'
		THEN StudentTestScore.numscore ELSE NULL END AS SATTOTAL,--"Latest SAT Total"
	StudentTest.TEST_DATE
	row_number() over(partition by student_number order by test_date desc) rn
from
	students
		LEFT JOIN StudentTestScore ON StudentTestScore.StudentID = Students.ID
		LEFT JOIN StudentTest ON StudentTestScore.StudentTestID = StudentTest.ID
		LEFT JOIN Test ON StudentTest.TestID = Test.ID
		LEFT JOIN TestScore ON StudentTestScore.TestScoreID = TestScore.ID
WHERE
	StudentTestScore.NumScore > 0
	AND Students.STUDENT_NUMBER IN ('3023484','3010864')
)
WHERE
	rn=1 and
	( CMTSCIENCE > 0 OR SATTOTAL > 0 )
group by
	STUDENT_NUMBER
order by
	STUDENT_NUMBER

Open in new window

0
 
BasssqueAuthor Commented:
Closer but I still get the value of 1330 for the total when I need 1300 (see below) and also sometimes test_date is null which is why the RN is 2 and not 1

if I run the following query, I get the results below.  I need to return only the score of 1300 which has the most recent date.
select
STUDENT_NUMBER as "StuNum",
max(CMTSCIENCE) AS "Grade 8 CMT Science",
SATTOTAL AS "Latest SAT Total",
max(TEST_DATE) as test_date,
min(rn) as rn

FROM
(

select 
students.student_number AS STUDENT_NUMBER,

CASE WHEN UPPER(Test.Name) = 'CMT' AND StudentTest.GRADE_LEVEL = '8' AND TestScore.NAME = 'Science Scale Score'
THEN StudentTestScore.numscore ELSE NULL END AS CMTSCIENCE,--"Grade 8 CMT Science"

CASE WHEN UPPER(Test.Name) = 'SAT' AND TestScore.NAME = 'Total Score'
THEN StudentTestScore.numscore ELSE NULL END AS SATTOTAL,--"Latest SAT Total"

StudentTest.TEST_DATE,
row_number() over(partition by student_number order by test_date desc) rn

from
students
LEFT JOIN StudentTestScore ON StudentTestScore.StudentID = Students.ID
LEFT JOIN StudentTest ON StudentTestScore.StudentTestID = StudentTest.ID
LEFT JOIN Test ON StudentTest.TestID = Test.ID
LEFT JOIN TestScore ON StudentTestScore.TestScoreID = TestScore.ID

WHERE
StudentTestScore.NumScore > 0
AND Students.STUDENT_NUMBER IN ('3023484','3010864')
AND (StudentTest.TEST_DATE IS NOT NULL AND StudentTestScore.numscore IS NOT NULL)
)

WHERE
--rn=1 AND
(CMTSCIENCE > 0 OR SATTOTAL > 0 AND TEST_DATE IS NOT NULL)

group by
STUDENT_NUMBER,SATTOTAL

order by
STUDENT_NUMBER

Open in new window


results
StuNum      Grade 8 CMT Science      Latest SAT Total            TEST_DATE            RN
3010864                                                     1300                              2016-10-01               2
3010864                                                     1320                              2016-06-04            8
3010864                                                     1330                              2016-03-02            21
3010864                    361                                                                    2013-03-15            36
3023484              247                                                                    2016-03-15            19
0
 
slightwv (䄆 Netminder) Commented:
Put rn=1 back in the where clause and

Change:
row_number() over(partition by student_number order by test_date desc) rn


To:
row_number() over(partition by student_number order by test_date desc nulls last) rn
0
 
BasssqueAuthor Commented:
ok, I changed the partition by clause

but with rn=1 I still get no results
If I take out where rn=1 then the row numbers are still the same as the results from my last post (see below).  I don't think this will work though because I still need row number 36 and 19 to return the CMT Science scores.

current results
StuNum      Grade 8 CMT Science      Latest SAT Total            TEST_DATE            RN
3010864                                                     1300                              2016-10-01               2
3010864                                                     1320                              2016-06-04            8
3010864                                                     1330                              2016-03-02            21
3010864                    361                                                                    2013-03-15            36
3023484              247                                                                    2016-03-15            19

expected results
StuNum      Grade 8 CMT Science      Latest SAT Total            TEST_DATE            RN
3010864                                                     1300                              2016-10-01               2
3010864                    361                                                                    2013-03-15            36
3023484              247                                                                    2016-03-15            19
0
 
slightwv (䄆 Netminder) Commented:
I could keep guessing all evening and still be wrong.

Please post the actual output of your original query or some sample data and the expected results from that output.

I need something to set up a test case with so I can post tested code.
0
 
BasssqueAuthor Commented:
Query
select
STUDENT_NUMBER as "StuNum",
max(CMTSCIENCE) AS "Grade 8 CMT Science",
SATTOTAL AS "Latest SAT Total",
max(TEST_DATE) as test_date

FROM
(

select 
students.student_number AS STUDENT_NUMBER,

CASE WHEN UPPER(Test.Name) = 'CMT' AND StudentTest.GRADE_LEVEL = '8' AND TestScore.NAME = 'Science Scale Score'
THEN StudentTestScore.numscore ELSE NULL END AS CMTSCIENCE,--"Grade 8 CMT Science"

CASE WHEN UPPER(Test.Name) = 'SAT' AND TestScore.NAME = 'Total Score'
THEN StudentTestScore.numscore ELSE NULL END AS SATTOTAL,--"Latest SAT Total"

max(StudentTest.TEST_DATE) as TEST_DATE

from
students
LEFT JOIN StudentTestScore ON StudentTestScore.StudentID = Students.ID
LEFT JOIN StudentTest ON StudentTestScore.StudentTestID = StudentTest.ID
LEFT JOIN Test ON StudentTest.TestID = Test.ID
LEFT JOIN TestScore ON StudentTestScore.TestScoreID = TestScore.ID

WHERE
StudentTestScore.NumScore > 0
AND Students.STUDENT_NUMBER IN ('3023484','3010864')
AND (StudentTest.TEST_DATE IS NOT NULL AND StudentTestScore.numscore IS NOT NULL)

group by
students.student_number,
Test.Name,
StudentTest.GRADE_LEVEL,
TestScore.NAME,
StudentTestScore.numscore

)

WHERE
(CMTSCIENCE > 0 OR SATTOTAL > 0 AND TEST_DATE IS NOT NULL)

group by
STUDENT_NUMBER,SATTOTAL

order by
STUDENT_NUMBER

Open in new window


Current Results
3010864            1300      2016-10-01 00:00:00.0
3010864            1320      2016-06-04 00:00:00.0
3010864            1330      2016-03-02 00:00:00.0
3010864              361            2013-03-15 00:00:00.0
3023484              247            2016-03-15 00:00:00.0

Expected Results
3010864            1300      2016-10-01 00:00:00.0
3010864              361            2013-03-15 00:00:00.0
3023484              247            2016-03-15 00:00:00.0
0
 
slightwv (䄆 Netminder) Commented:
Based on what you posted, what are the rules that get me from current to expected?

I need data that helps me get to a testable solution for you.  The current results you posted have the MAX date column back in there.

Also those expected results are very different from the previous expected results.

I need a simple test case that I can create on my side.  I can then take your basic SQL and tweak it.

There is nothing here for me to work with.
0
 
BasssqueAuthor Commented:
I'll work on that first thing in the morning and get something posted for you.  Is there an easy way I can just export it based on the tables referenced in my query?
0
 
slightwv (䄆 Netminder) Commented:
I don't need and really don't want 5 tables worth of data.

I just need a very simple example that shows what you have and what you need.

Take the inner query and provide some example test data from that.

The from the data, post the expected results.  Feel free to see it with whatever edge cases you want covered.

For example start with some output from:
select 
	students.student_number AS STUDENT_NUMBER,
	CASE WHEN UPPER(Test.Name) = 'CMT' AND StudentTest.GRADE_LEVEL = '8' AND TestScore.NAME = 'Science Scale Score'
		THEN StudentTestScore.numscore ELSE NULL END AS CMTSCIENCE,--"Grade 8 CMT Science"
	CASE WHEN UPPER(Test.Name) = 'SAT' AND TestScore.NAME = 'Total Score'
		THEN StudentTestScore.numscore ELSE NULL END AS SATTOTAL,--"Latest SAT Total"
	StudentTest.TEST_DATE
from
students
	LEFT JOIN StudentTestScore ON StudentTestScore.StudentID = Students.ID
	LEFT JOIN StudentTest ON StudentTestScore.StudentTestID = StudentTest.ID
	LEFT JOIN Test ON StudentTest.TestID = Test.ID
	LEFT JOIN TestScore ON StudentTestScore.TestScoreID = TestScore.ID
WHERE
StudentTestScore.NumScore > 0
AND Students.STUDENT_NUMBER IN ('3023484','3010864')

Open in new window


Then I can create a single table and load it with your provided results.
0
 
BasssqueAuthor Commented:
I hope this is what you are looking for, let me know.

Below is the table data
STUDENTID,TESTSCOREID,STUDENTTESTID,NUMSCORE,TESTID,TEST_DATE,SCHOOLID,GRADE_LEVEL,TERMID,TEST_NAME,SCORE_NAME
3522,122,86714,1300,104,2016-10-01 00:00:00,0,12,2600,SAT,Total Score
3522,122,64556,1320,104,2016-06-04 00:00:00,0,11,2500,SAT,Total Score
3522,122,86580,1330,104,2016-03-02 00:00:00,0,11,2500,SAT,Total Score
3523,122,86714,1200,104,2016-03-02 00:00:00,0,11,2500,SAT,Total Score
3523,122,64556,1220,104,2016-06-04 00:00:00,0,11,2500,SAT,Total Score
3523,122,86580,1230,104,2016-10-02 00:00:00,0,11,2500,SAT,Total Score
3524,122,86714,1100,104,2016-03-02 00:00:00,0,12,2600,SAT,Total Score
3524,122,64556,1130,104,2016-06-04 00:00:00,0,12,2600,SAT,Total Score
3524,122,86580,1120,104,2016-10-02 00:00:00,0,12,2600,SAT,Total Score

Open in new window


What I need is the numscore associated with the most recent test_date.  The output would look like
3522,1300
3523,1230
3524,1120
0
 
slightwv (䄆 Netminder) Commented:
Below is my complete test case and the SQL that gives me your expected results.

Since it is the same row_number I suggested above that didn't work for you, I'll need you to add values to the test case where the SQL no longer provides the expected results.

drop table tab1 purge;
create table tab1(
	STUDENTID number,
	TESTSCOREID number,
	STUDENTTESTID number,
	NUMSCORE number,
	TESTID number,
	TEST_DATE date,
	SCHOOLID number,
	GRADE_LEVEL number,
	TERMID number,
	TEST_NAME varchar2(3),
	SCORE_NAME varchar2(11)
);

insert into tab1 values(3522,122,86714,1300,104,to_date('2016-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,12,2600,'SAT','Total Score');
insert into tab1 values(3522,122,64556,1320,104,to_date('2016-06-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,11,2500,'SAT','Total Score');
insert into tab1 values(3522,122,86580,1330,104,to_date('2016-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,11,2500,'SAT','Total Score');
insert into tab1 values(3523,122,86714,1200,104,to_date('2016-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,11,2500,'SAT','Total Score');
insert into tab1 values(3523,122,64556,1220,104,to_date('2016-06-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,11,2500,'SAT','Total Score');
insert into tab1 values(3523,122,86580,1230,104,to_date('2016-10-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,11,2500,'SAT','Total Score');
insert into tab1 values(3524,122,86714,1100,104,to_date('2016-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,12,2600,'SAT','Total Score');
insert into tab1 values(3524,122,64556,1130,104,to_date('2016-06-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,12,2600,'SAT','Total Score');
insert into tab1 values(3524,122,86580,1120,104,to_date('2016-10-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),0,12,2600,'SAT','Total Score');
commit;

/*
What I need is the numscore associated with the most recent test_date.  The output would look like
3522,1300
3523,1230
3524,1120 
*/

select studentid, numscore
from (
	select studentid, numscore, row_number() over(partition by studentid order by test_date desc) rn from tab1
)
where rn=1
/

Open in new window

0
 
BasssqueAuthor Commented:
That works if I just query this score by itself.  Once I try to add other scores it gets all messed up.  I'll just join individual queries in SSIS rather than fight with doing it all in one query.  This works for these score by themselves.  Thanks for your time, I appreciate it!
0
 
slightwv (䄆 Netminder) Commented:
My guess is a single query will be a much better solution from both complexity and performance.

I'm happy to re-open this and continue working on it with you.

I just need a test case so I can provide tested SQL.  Add to the test case with the "messed up" data and update the expected results.

I don't believe the final SQL is as complex as you seem to think it is.
0
 
BasssqueAuthor Commented:
We might as well finish what we started.  Thank you!

From the table data below, I'd need the most recent NUMSCORE by TEST_DATE for each TESTSCOREID

STUDENTID,TESTSCOREID,STUDENTTESTID,NUMSCORE,TESTID,TEST_DATE,SCHOOLID,GRADE_LEVEL,TERMID,TEST_NAME,SCORE_NAME
3522,122,86714,1300,104,2016-10-01 00:00:00,0,12,2600,SAT,Total Score
3522,122,64556,1320,104,2016-06-04 00:00:00,0,11,2500,SAT,Total Score
3522,122,86580,1330,104,2016-03-02 00:00:00,0,11,2500,SAT,Total Score
3523,122,86714,1200,104,2016-03-02 00:00:00,0,11,2500,SAT,Total Score
3523,122,64556,1220,104,2016-06-04 00:00:00,0,11,2500,SAT,Total Score
3523,122,86580,1230,104,2016-10-02 00:00:00,0,11,2500,SAT,Total Score
3524,122,86714,1100,104,2016-03-02 00:00:00,0,12,2600,SAT,Total Score
3524,122,64556,1130,104,2016-06-04 00:00:00,0,12,2600,SAT,Total Score
3524,122,86580,1120,104,2016-10-02 00:00:00,0,12,2600,SAT,Total Score
3522,121,86714,500,104,2016-10-01 00:00:00,0,12,2600,SAT,Math Score
3522,121,64556,520,104,2016-06-04 00:00:00,0,11,2500,SAT,Math Score
3522,121,86580,530,104,2016-03-02 00:00:00,0,11,2500,SAT,Math Score
3523,121,86714,500,104,2016-03-02 00:00:00,0,11,2500,SAT,Math Score
3523,121,64556,520,104,2016-06-04 00:00:00,0,11,2500,SAT,Math Score
3523,121,86580,530,104,2016-10-02 00:00:00,0,11,2500,SAT,Math Score
3524,121,86714,500,104,2016-03-02 00:00:00,0,12,2600,SAT,Math Score
3524,121,64556,530,104,2016-06-04 00:00:00,0,12,2600,SAT,Math Score
3524,121,86580,520,104,2016-10-02 00:00:00,0,12,2600,SAT,Math Score
3522,120,86714,800,104,2016-10-01 00:00:00,0,12,2600,SAT,EBRW Score
3522,120,64556,800,104,2016-06-04 00:00:00,0,11,2500,SAT,EBRW Score
3522,120,86580,800,104,2016-03-02 00:00:00,0,11,2500,SAT,EBRW Score
3523,120,86714,700,104,2016-03-02 00:00:00,0,11,2500,SAT,EBRW Score
3523,120,64556,700,104,2016-06-04 00:00:00,0,11,2500,SAT,EBRW Score
3523,120,86580,700,104,2016-10-02 00:00:00,0,11,2500,SAT,EBRW Score
3524,120,86714,600,104,2016-03-02 00:00:00,0,12,2600,SAT,EBRW Score
3524,120,64556,600,104,2016-06-04 00:00:00,0,12,2600,SAT,EBRW Score
3524,120,86580,600,104,2016-10-02 00:00:00,0,12,2600,SAT,EBRW Score

Open in new window


expected output
3522,1300,500,800
3523,1230,530,700
3524,1120,520,600
0
 
slightwv (䄆 Netminder) Commented:
Give me a few minutes to look over what you posted.
0
 
BasssqueAuthor Commented:
This is perfect and helps us a great deal!!  Thank you so much for your time and expertise!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.