Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need help with limiting records returned in the query

Posted on 2016-09-23
3
Medium Priority
?
65 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 1600 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 400 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 September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

715 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