Solved

return result by latest date - oracle query

Posted on 2016-10-31
21
49 Views
Last Modified: 2016-11-01
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

0
Comment
Question by:Basssque
  • 11
  • 9
21 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867467
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
 

Author Comment

by:Basssque
ID: 41867475
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867485
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
 

Author Comment

by:Basssque
ID: 41867502
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867508
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
 

Author Comment

by:Basssque
ID: 41867516
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867529
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
 

Author Comment

by:Basssque
ID: 41867540
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867612
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
 

Author Comment

by:Basssque
ID: 41867621
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41867648
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
 

Author Comment

by:Basssque
ID: 41868418
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41868439
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
 

Author Comment

by:Basssque
ID: 41868529
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41868535
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
 

Author Comment

by:Basssque
ID: 41868664
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41868673
Give me a few minutes to look over what you posted.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41868938
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
 

Author Closing Comment

by:Basssque
ID: 41868999
This is perfect and helps us a great deal!!  Thank you so much for your time and expertise!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41869045
Glad to help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now