Solved

need help with limiting records returned in the query

Posted on 2016-09-23
3
63 Views
Last Modified: 2016-10-12
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'
0
Comment
Question by:happylife1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points (awarded by participants)
ID: 41812752
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 100 total points (awarded by participants)
ID: 41812902
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

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question