user2040021 user2040021 - 1 month ago 5
SQL Question

Multiple where condition to get phone number from the client contact table

I need your help in order to get phone number from client contact table which is in 1:n relationship with Client table and they join by client name

Here is the data from client contact table
enter image description here

I would like to get following as result.

enter image description here

The problem is how to put the condtion.

I am getting what I want plus one row which is 1st row from client Peter Masae where PRIMARY=no , Phone Location = primary and PhoneLocationDD= primary.

please help.

I join this table to Client table on Name column and I need at least one number for each client and to get those first priority to get the number is where it says PRIMARY=yes and Phone location = Primary

But if record does't have that then look for
PRIMARY=no and Phone location = Primary

Please help!

Answer

I suspect this is what you want:

select cc.*
from (select cc.*,
             row_number() over (partition by name
                                order by (case when primary = 'Yes' then 1 else 2 end),
                                         (case when phonelocation = 'primary' then 1 else 2 end),
                                         (case when phonelocationdd = 'primary' then 1 else 2 end)
                               ) as seqnum
      from clientcontact cc
     ) cc
where seqnum = 1;
Comments