Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
towardabettercountry
Starting Member
26 Posts |
 Posted - 2012-02-09 : 10:28:24
|
| Hi Folks - I've got a query I'm beating my head against :-) This is such a newbie error, I'm sure - but here it is:I'm needing to get the most recent Admission_Exam_Date per Student_ID. However, running the query below still includes the other dates the exam was taken when I include Admission_Exam_Score. Any thoughts on how I can get the most recent exam for each student? Thanks you very much for any help! - MasonSELECTStudent_ID AS CVPA_SIS_Exam_Student_ID ,Admission_Exam_Code AS CVPA_SIS_Exam_Admission_Exam_Code ,Admission_Exam AS CVPA_SIS_Exam_Admission_Exam ,Admission_Exam_Type AS CVPA_SIS_Exam_Admission_Exam_Type ,MAX(Admission_Exam_Date) AS CVPA_SIS_MAX_Exam_Admission_Exam_Date ,Admission_Exam_Score AS CVPA_SIS_Exam_Admission_Exam_Score FROM AR_Exam AS AR_Exam1GROUP BYStudent_ID,Admission_Exam_Code,Admission_Exam,Admission_Exam_Type,Admission_Exam_Score |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-09 : 12:42:23
|
| [code]SELECTFROM(SELECTStudent_ID AS CVPA_SIS_Exam_Student_ID ,Admission_Exam_Code AS CVPA_SIS_Exam_Admission_Exam_Code ,Admission_Exam AS CVPA_SIS_Exam_Admission_Exam ,Admission_Exam_Type AS CVPA_SIS_Exam_Admission_Exam_Type ,Admission_Exam_Date AS CVPA_SIS_MAX_Exam_Admission_Exam_Date ,Admission_Exam_Score AS CVPA_SIS_Exam_Admission_Exam_Score ,ROW_NUMBER() OVER (PARTITION BY Student_ID ORDER BY Admission_Exam_Date DESC) AS RnFROM AR_Exam AS AR_Exam1)tWHERE Rn=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Query Troubles