Solved

need help with limiting records returned in the query

Posted on 2016-09-23
3
53 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
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 40

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

821 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