March 20, 2012 at 1:38 pm
What is your expected result out of the sample data you provide. I think i may have solution for it, but need to see your desired result visually..
March 20, 2012 at 1:59 pm
I'm not sure if my approach is waht you're lloking for, but I think the ROW_NUMBER approach will help here:
;
WITH cte AS
(
SELECT
Student_ID,
Exam_ID,
Admission_Exam_Code,
Admission_Exam,
Admission_Exam_Type,
Admission_Exam_Date,
Admission_Exam_Score,
ROW_NUMBER() OVER(PARTITION BY Student_ID,Exam_ID ORDER BY Admission_Exam_Date desc ) pos
FROM @Admission_Exam_Test
)
SELECT *
FROM cte
WHERE pos = 1
March 20, 2012 at 3:07 pm
pietlinden (2/13/2012)
I'm needing to get the most recent AP_Ex.Ap_Expt_Date for each AP_Ex.Student_IDSELECT Max(AP_Ex.Ap_Expt_Date) As LatestDate, AP_Ex.Student_ID
FROM...
GROUP BY AP_Ex.Student_ID;
What I used to do was select the latest test date for each student, then using the latest test date and student id for each student, select the rest of the data. Obviously you'd have to disambiguate the test if multiple tests per date.
-- something like:
CREATE TABLE ZTMP_TEST_TABLE
(
Student_ID VARCHAR(10),
Ap_Expt_Date DATETIME,
Test_Score VARCHAR(10)
)
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','2012-01-01','10')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','2012-02-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','2012-02-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','2012-04-01','13')
SELECT student_id_sel, LatestDate, ZTMP_TEST_TABLE.* FROM
(
SELECT Max(AP_Ex.Ap_Expt_Date) As LatestDate, AP_Ex.Student_ID as student_id_sel
FROM ZTMP_TEST_TABLE AP_Ex
GROUP BY AP_Ex.Student_ID
) SELECTED
JOIN
ZTMP_TEST_TABLE
ON
student_id_sel = Student_ID AND
LatestDate = Ap_Expt_Date
March 20, 2012 at 3:39 pm
Thanks for the reply. The results should be something like this: (I apologize for the format - I can't for the life of me figure how to make it look clean)
Student_IDExam_IDAdmission_Exam_CodeAdmission_ExamAdmission_Exam_TypeMost_Recent_Exam_Date Admission_Exam_Score
240043 10 MCATBIO MCAT Biology Score Score 2011-08-01 09
240043 2 MCATTOT MCAT Total Score 2011-08-01 26
240043 7 MCATVRBL MCAT Verbal Score Score 2011-08-01 07
240043 8 MCATPHYS MCAT Physical Science Score 2011-08-01 10
440643 10 MCATBIO MCAT Biology Score Score 2011-09-01 04
440643 2 MCATTOT MCAT Total Score 2011-09-01 30
440643 7 MCATVRBL MCAT Verbal Score Score 2011-09-01 08
440643 8 MCATPHYS MCAT Physical Science Score 2011-09-01 12
March 20, 2012 at 3:47 pm
March 20, 2012 at 3:51 pm
Just to expand on my other post, I used to get the latest instance of whatever I wanted, then use that latest instance as sort of a lookup key back into the same table. So if I had test types also, I'd just include that into the group by clause to find the latest instance, then also use it in the join.
CREATE TABLE ZTMP_TEST_TABLE
(
Student_ID VARCHAR(10),
Test_ID VARCHAR(10),
Ap_Expt_Date DATETIME,
Test_Score VARCHAR(10)
)
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','MATH','2012-01-01','10')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','MATH','2012-02-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','MATH','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','ENG','2012-01-01','13')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','ENG','2012-02-01','14')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','ENG','2012-03-01','15')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','RDG','2012-01-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','RDG','2012-02-01','13')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0001','RDG','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','MATH','2012-02-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','MATH','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','MATH','2012-04-01','13')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','ENG','2012-02-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','ENG','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','ENG','2012-04-01','13')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','RDG','2012-02-01','11')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','RDG','2012-03-01','12')
INSERT INTO ZTMP_TEST_TABLE VALUES ('0002','RDG','2012-04-01','13')
SELECT student_id_sel, test_id_sel, LatestDate, ZTMP_TEST_TABLE.* FROM
(
SELECT Max(Ap_Expt_Date) As LatestDate, Student_ID as student_id_sel, Test_ID as test_id_sel
FROM ZTMP_TEST_TABLE
GROUP BY Student_ID, Test_ID
) SELECTED
JOIN
ZTMP_TEST_TABLE
ON
student_id_sel = Student_ID AND
test_id_sel = Test_ID AND
LatestDate = Ap_Expt_Date
I still tend to use this approach because I have so many 2000 level servers to support, but its worth noting that the over() clause works nicely too, I just haven't used it as much.
March 20, 2012 at 3:59 pm
Lutz,
Yes, I was working through the replies in order. You nailed it! I know you're probably busy, but would you mind explaining how it works? I don't understand 'WITH cte AS' or 'WHERE pos = 1'. I'll look for an article on ROW_NUMBER() - I've never seen that used before. Either way, that's the result set I'm looking for.
Mason
March 20, 2012 at 4:12 pm
Here are some resources for cte:
BooksOnLine and a article[/url] here at SSC.
and ROW_NUMBER
BooksOnLine and a related article
March 21, 2012 at 7:48 am
Thanks to everyone for the help!!
March 27, 2012 at 11:06 am
One more question on this. My row_number doesn't seem to be working in a few instances. Here's what I have:
WITH cte AS
(
SELECT
'CVPA~SIS~AD~Ad_Exam~' + CAST(Ad_Exam.Admission_Exam_ID AS NVARCHAR) AS PKEY_CVPA_SIS_AD_Admission_Exam
,'CVPA~SIS~GI~Student~' + CAST(Ad_Exam.Student_ID AS NVARCHAR) AS FKEY_CVPA_SIS_GI_Student
,Ad_Exam.Admission_Exam_ID
,Ad_Exam.Student_ID AS Student_ID_1
,Ad_Exam.Enrollment_ID
,Ad_Exam.Exam_ID
,Ad_Exam.Admission_Exam_Code
,Ad_Exam.Admission_Exam
,Ad_Exam.Admission_Exam_Type
,Ad_Exam.Student_Exam_Required_Ind
,Admission_Exam_Date =
CASE WHEN Ad_Exam.Admission_Exam_Date IS NULL THEN '1/1/2006'
ELSE Ad_Exam.Admission_Exam_Date END
,Ad_Exam.Admission_Exam_Minimum_Score
,Ad_Exam.Admission_Exam_Score
,Ad_Exam.Admission_Exam_Scheduled_Date
,Ad_Exam.Admission_Exam_Fee_Amount
,Ad_Exam.Admission_Exam_Fee_Paid_By_Student_Ind
,Ad_Exam.Admission_Exam_Location_Code
,Ad_Exam.Admission_Exam_Location
,Ad_Exam.Admission_Exam_Result_Date
,Ad_Exam.Admission_Exam_Number_Result
,Ad_Exam.Admission_Exam_Text_Result
,Ad_Exam.Parent_Exam_ID
,Ad_Exam.CVue_Record_Added_Date
,Ad_Exam.CVue_Record_Modified_DateTime
,Ad_Exam.DW_Modified_DateTime
FROM Admission_Exam AS Ad_Exam
/*WHERE added because result set was too large*/
WHERE
Admission_Exam = 'Cum GPA' OR
Admission_Exam = 'DAT A Academic Average Score' OR
Admission_Exam = 'DMD - Cum GPA' OR
Admission_Exam = 'DMD - Science GPA' OR
Admission_Exam = 'DO - Cum GPA' OR
Admission_Exam = 'DO - Science GPA' OR
Admission_Exam = 'DOR - Cum GPA' OR
Admission_Exam = 'EPAC Overall Score' OR
Admission_Exam = 'GRE Analytical Score' OR
Admission_Exam = 'GRE Quantitative Score' OR
Admission_Exam = 'GRE Verbal Score' OR
Admission_Exam = 'MCAT Total' OR
Admission_Exam = 'OT - Cum GPA' OR
Admission_Exam = 'OT - Science GPA' OR
Admission_Exam = 'PA - Cum GPA' OR
Admission_Exam = 'PA - Science GPA' OR
Admission_Exam = 'PT - Cum GPA' OR
Admission_Exam = 'PT - Prereq GPA' OR
Admission_Exam = 'Science GPA'
SELECT
cte.Student_ID_1 AS Student_ID_2
,cte.Exam_ID
,cte.Admission_Exam_Code
,cte.Admission_Exam
,cte.Admission_Exam_Type
,cte.Admission_Exam_Date
,CONVERT(decimal(4,2),cte.Admission_Exam_Score) AS Admission_Exam_Score
,ROW_NUMBER() OVER(PARTITION BY cte.Student_ID_1,cte.Exam_ID
ORDER BY cte.Admission_Exam_Score desc ) pos
FROM cte
Here is a sample of results:
Student_ID_2Exam_ID Admission_Exam_Code Admission_ExamAdmission_Exam_TypeAdmission_Exam_DateAdmission_Exam_Scorepos
634456781MCATTOT MCAT Total Score 2011-01-01 28.00 1
634456781MCATTOT MCAT Total Score 2011-07-01 22.00 2
6344567859DOCUMGPA DO - Cum GPA Score 2006-01-01 3.48 1
6344567860DOSCIGPA DO - Science GPAScore 2006-01-01 3.16 1
569477641MCATTOT MCAT Total Score 2011-05-01 24.00 1
569477641MCATTOT MCAT Total Score 2010-06-01 29.00 2
569477641MCATTOT MCAT Total Score 2009-06-01 24.00 3
5694776459DOCUMGPA DO - Cum GPA Score 2006-01-01 3.84 1
141353 60DOSCIGPA DO - Science GPAScore 2006-01-01 3.19 1
Notice that (for the first Student_ID_2) has an Admission_Exam_Date of 2011-07-01 in pos 2. However, I thought the desc syntax would give me the most recent date, which should put 2011-07-01 in pos 1. (Note that for the second Student_ID_2 set, it did work properly). I'm probably making a newbie error somewhere in here, but maybe someone can help me out.
By the way, does anyone have a link on an easy way to post a result set in the forum? The above is probably the most hacked together result set to ever grace this forum 🙂
March 27, 2012 at 11:12 am
According to your post you have:
...
ORDER BY cte.Admission_Exam_Score desc ) pos
which looks like its not the date 😉
March 27, 2012 at 11:57 am
patrickmcginnis59 (3/27/2012)
According to your post you have:
...
ORDER BY cte.Admission_Exam_Score desc ) pos
which looks like its not the date 😉
As I was saying... 🙂
Thanks so much for the help. I'll get the hang of this yet.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply