Link to home
Start Free TrialLog in
Avatar of guinnie
guinnie

asked on

How to select data based on lowest value in a column

The value in the column can be from 1 to 100. I need to select the lowest value.
For example, in table named student contacts there is a column 'primary_contact_priority' which assigns a number value to a student
guardian. This could be a parent, grandparent or otherwise. For some students the value  'primary_contact_priority' may not have a '1'
value but may start at any higher number.
In the table student contacts the column 'contact_person_id' is the guardian assigned id and the 'student_person_id' is the student id.
I've attached the sql code. I need to implement code as needed to select minimum as described above.
jk_parent_emails.sql
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

Open in new window

Avatar of guinnie

ASKER

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?
Avatar of guinnie

ASKER

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'.
Avatar of guinnie

ASKER

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
Avatar of guinnie

ASKER

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 ?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of guinnie

ASKER

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.
Avatar of guinnie

ASKER

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