Hi, I have the following data :
E.g. Table XX looks like
Code:
ID CLASS_ID STUDENT_ID COMPLETED_DATE
1 5000 100 6/17/2004 9:55:08 AM
2 5000 101 3/3/2004 3:51:02 PM
3 5000 102 3/11/2004 1:59:38 PM
4 5000 100 3/26/2004 12:15:11 PM
5 5000 101 4/30/2004 1:09:59 PM
Every student can take the same test again and again and I need to get the latest entry for every student. The output should be like :
Code:
ID CLASS_ID STUDENT_ID COMPLETED_DATE
1 5000 100 6/17/2004 9:55:08 AM
3 5000 102 3/11/2004 1:59:38 PM
5 5000 101 4/30/2004 1:09:59 PM
What is the cleaner way to achieve this ? I can do it using Inline Views as here, but was wondering if there is any other better way to do this.
Code:
select ar.id, ar.class_id, ar.student_id, ar.completed_date from clg_assessment_result ar,
(SELECT ar.class_id, ar.student_id, max(ar.completed_date) as latest_completed_date
FROM clg_assessment_result ar
where ar.ASSIGNMENT_SEQUENCE = 1 and ar.status = 20
GROUP BY ar.class_id, ar.student_id) inner_ar
where ar.class_id = inner_ar.class_id and ar.student_id = inner_ar.student_id and ar.completed_date = inner_ar.latest_completed_date
Thanks.