CODEWITHSUNDEEP
sqlsql-servert-sql
user2040021
user2040021

Reputation: 309

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!

Upvotes: 0

Views: 213

Answers (2)

Parshuram Kalvikatte
Parshuram Kalvikatte

Reputation: 1646

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY PRIMARY DESC) NUMBER,
    *
    FROM CLIENT_CONTACT
) X
WHERE X.PHONE_LOCATION = 'PRIMARY' AND X.NUMBER  = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270553

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;

Upvotes: 3

Related Questions