DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL Help.

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    SQL Help.

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use Analytical function which is faster.

    Code:
    select id, class_id, student_id, completed_date
    from ( select id, class_id, student_id,completed_date,
                  row_number () over
                  (partition by student_id, class_id
                   order by completed_date desc ) rn
     from my_table )
    where rn = 1
    /
    Tamil

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks Tamilselvan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width