SQL Query - Select Statement

I have a table where the data could be like these below:

CusID | Name | State | PhyAddress

160285 | FYZPZ | NULL | 0

160285 | FYZPZ | NJ | 1

160285 | FYZPZ | NJ | 1

or

CusID | Name | State | PhyAddress

160285 | FYZPZ | NJ xx | 1

or

CusID | Name | State | PhyAddress

160285 | FYZPZ | NULL | 0

160285 | FYZPZ | NJ xx | 0

I need to get the default state for the user. And the logic is:

The result should display the physcial state first (PhyAddress = 1) and if there is no physical a开发者_运维百科ddress (PhyAddress = 0), then it should display the mailing adress state (PhyAddress = 0). If there are none, the state can be blank.

There are multiple customers in the table and they each can have multiple rows like above and I need to get the default state for each customer.

The resuts needs to show a result of CusID, Name, State

Please help me with the query. TIA

use (EDIT after comments):

SELECT DISTINCT Y.CusID, Y.Name, ISNULL (Y.S1, Y.S0) AS State
FROM
(
SELECT
X.CusID,
X.Name,
( SELECT MAX ( State ) FROM Address T WHERE T.PhyAddress = 1 AND T.CusID = X.CusID ) AS S1,
( SELECT MAX ( State ) FROM Address T WHERE T.PhyAddress = 0 AND T.CusID = X.CusID ) AS S0
FROM Address AS X
) AS Y

Search the maximum value of PhyAddress:

SELECT CusID, 
       State
FROM table t1
WHERE PhyAddress = 
    (
        SELECT MAX(t2.PhyAddress)
        FROM table t2
        WHERE t2.CusID = t1.CusID
    )

What have you tried?

let me give you something to get you going

 Select CusID, Name, case when phyaddress = something then something
        when
        when
        ELSE ' ' END as State

from sometable

where something

You could do something like

select CusID, Name, State, PhyAddress from sometable where (CusID, PhyAddress) in
  (select CusID, max(PhyAddress) from sometable)
  group by CusID, Name, State, PhyAddress