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'
happylife1234Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try this (untested, just typed it in):
select e1.email_email_address, s.student_name from 
 student s 
 left outer join (select email_id, email_email_address, email_email_code, row_number() over(partition by email_id order by email_id, email_activity_date desc) rn from email where email_email_code='PERS'  ) e1
                  on s.student_id = e1.email_id and
                   e1.rn=1
          where 
                s.student_coll_code='BU' 

Open in new window

0
 
SharathConnect With a Mentor Data EngineerCommented:
In slightwv's post, there is no harm keeping email_id in the ORDER BY of ROW_NUMBER because the rows are partitioned on email_id only. But you can take away that.
select e1.email_email_address, s.student_name from 
 student s 
 left outer join (select email_id, email_email_address, email_email_code, row_number() over(partition by email_id order by email_activity_date desc) rn from email where email_email_code='PERS'  ) e1
                  on s.student_id = e1.email_id and
                   e1.rn=1
          where 
                s.student_coll_code='BU' 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.