Solved

return result by latest date - oracle query

Posted on 2016-10-31
21
103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
21 Comments
 
LVL 77

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 77

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 77

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 77

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 77

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
 
LVL 77

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 77

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 77

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 77

Expert Comment

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

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 77

Expert Comment

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

Featured Post

How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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
Suggested Courses

630 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