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
I would like to get following as result.
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.
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
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;