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
Asked
Active
Viewed 50 times
0
-
2Which dbms are you using? – jarlh May 12 '22 at 18:06
-
`with data as (select *, row_number() over (partition by "Account Number" order by Override desc) as rn from T) select * from data where rn = 1` – shawnt00 May 12 '22 at 18:19
-
@shawnt00 true if the maximum value of `Override` equals to `1` – Barbaros Özhan May 12 '22 at 18:23
-
SQL Server 2016 – user2274800 May 12 '22 at 18:27
-
1`with data as (select *, row_number() over (partition by "Account Number" order by case when Override = 1 then 1 else 2 end) as rn from T) select * from data where rn = 1` – shawnt00 May 12 '22 at 18:30
-
What about when you have three addresses? – Sean Lange May 12 '22 at 20:02
1 Answers
0
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:
systematical
- 43
- 5