happylife1234
asked on
need help with limiting records returned in the query
I have an student table and email table. The email table has several PERS emails for each student among other emails. I need a list of all students and one PERS email for the students. I try to pick the latest PERS email. However some students have more than one PERS email record for the same date. How do I say to just pick one of the many in this case.
select e1.email_email_address, s.student_name from
student s
left outer join email e1
on s.student_id = e1.email_id and
e1.email_emal_code='PERS' and
e1.email_activity_date =( select max(e2.email_activity_date ) from email e2 where e2. email_id = s.student_id and e2.email_emal_code='PERS' )
where
s.student_coll_code='BU'
select e1.email_email_address, s.student_name from
student s
left outer join email e1
on s.student_id = e1.email_id and
e1.email_emal_code='PERS' and
e1.email_activity_date =( select max(e2.email_activity_date
where
s.student_coll_code='BU'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.