Link to home
Start Free TrialLog in
Avatar of happylife1234
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'
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial