How to select data based on lowest value in a column

Use row_number() in an inline view then only select where rn=1.

I might have the partition by column wrong and cannot test this but you get the idea:

select school_code, parent_name,primary_contact_priority,contact_person_id,
      email,
      student_name
from (
select Schools.school_code,pers.preferred_name_upper parent_name,student_contacts.primary_contact_priority,student_contacts.contact_person_id,
      person_telecom.email_account email,
      students.preferred_name_upper student_name,
      row_number() over(partition by students.person_id, order by primary_contact_priority asc) rn
FROM  persons pers
      Inner join student_contacts student_contacts
            on  student_contacts.contact_person_id = pers.person_id
      Inner join STUDENT_REGISTRATIONS student_registrations
            on STUDENT_REGISTRATIONS.person_id = student_contacts.student_person_id
      LEFT OUTER JOIN Persons students
            on  STUDENT_REGISTRATIONS.person_id = students.person_id
      LEFT OUTER JOIN  person_telecom
            ON person_telecom.person_id = pers.person_id
      LEFT JOIN Schools schools
            on STUDENT_REGISTRATIONS.school_code = schools.school_code
where  
    STUDENT_REGISTRATIONS.school_year = '20162017'
and STUDENT_REGISTRATIONS.grade = 'JK'
and person_telecom.email_account is not null
)
where rn=1
order by student_name

Select allOpen in new window

Yes. This looks it will work. There is one glitch I need to figure out. In some cases the code is selecting
guardian with a value of 2 instead of 1. Not sure why but I'm troubleshooting now.

What is the data type of the guardian column?

Number (2,0)

Here is sample data where it is selecting wrong guardian. It is selecting father.
select contact_person_id, primary_contact_priority,student_person_id, relationship_type_name from student_contacts
where student_person_id = '0001351028'.

contact_person_id , primary_contact_priority ,student_person_id, relationship_type_name
------------------------------------------------------------------------------------------------------------------------------
0001351030                  2                                                0001351028                 Father
0001351032                 1                                                0001351028                 Mother

Post the resuts of:

select contact_person_id, primary_contact_priority,student_person_id, relationship_type_name,
   row_number() over(order by primary_contact_priority asc) rn
from student_contacts
where student_person_id = '0001351028'.

0001351032      1      0001351028      Mother      1
0001351030      2      0001351028      Father      2

Well, the order works.  Not sure why it doesn't in the larger query.

Not sure why this would change things but try changing the partitionfrom:
row_number() over(partition by students.person_id, order by primary_contact_priority asc) rn

to:
row_number() over(partition by student_contacts.contact_person_id, order by primary_contact_priority asc) rn

No that didn't work.
It seems to only return correct results when the lowest value of contact_person_id
has the lowest primary_contact_priority.
Is there a way to just use primary_contact_priority to partition ?

That is true about new contact. It is just an algorithm.
It is difficult to provide test data as data is very sensitive.
I can probably work with code to get it working.

Thank you for your timely feedback.
I appreciate greatly.

>>It is difficult to provide test data as data is very sensitive.

It doesn't have to be real data.

Just create a test case that is close to your actual tables and data that shows the order being off.

Ok. Give me just awhile to get data in xls and send