asked on

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

slightwv (䄆 Netminder)

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)
...

Basssque

ASKER

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.

slightwv (䄆 Netminder)

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

Basssque

ASKER

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

slightwv (䄆 Netminder)

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

Basssque

ASKER

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

slightwv (䄆 Netminder)

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.

Basssque

ASKER

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

slightwv (䄆 Netminder)

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.

Basssque

ASKER

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?

slightwv (䄆 Netminder)

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.

Basssque

ASKER

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

slightwv (䄆 Netminder)

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

Basssque

ASKER

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!

slightwv (䄆 Netminder)

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.

Basssque

ASKER

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

slightwv (䄆 Netminder)

Give me a few minutes to look over what you posted.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Basssque

ASKER

This is perfect and helps us a great deal!! Thank you so much for your time and expertise!

slightwv (䄆 Netminder)

Glad to help.