Unique Address SQL Query [duplicate]

Question

I have a table like this:

I would like one address for each account number, if the account number has two addresses, I want to get the one with the "OVERRIDE" column =1, otherwise if it has only address, then I would like to have it returned

Answer 1

Only the account no and override matter (for the purpose of the calculation). here is a link to the sql fiddle: http://sqlfiddle.com/#!18/b2bb8/1/0

select
  account
  ,street
  ,city
  ,zip
  ,override
 from (
select
  *
  , [filter] = max(override) over(partition by account)
from t1
) as t2
where override = filter

output: