I have the following sample query:
WITH a As ( SELECT '2020-04-01' as date,'test123' as id,'abc' as foo,10 as purchases Union all SELECT '2020-04-01', 'test123', 'abc', 0 Union all SELECT '2020-04-01', 'test123', 'abc', 0 ), b as ( SELECT '2020-04-01' as date,'test123' as id,'abc' as foo,50 as budget ) select a.date,a.id,a.foo,a.purchases,budget from a LEFT JOIN b ON concat(a.date,a.id)=concat(b.date,b.id)
and I’d like the following output
Row date,id,foo,purchases,budget 1 2020-04-01,test123,abc,10,50 2 2020-04-01,test123,abc,0,null 3 2020-04-01,test123,abc,0,null
I read many questions on the similar topic but I wasn’t able to make it work.
Advertisement
Answer
You can use row_number()
:
select a.date, a.id, a.foo, a.purchases, (case when a.seqnum = 1 then b.budget end) as budget from (seleect a.*, row_number() over (partition by date, id order by purchases desc) as seqnum from a ) a b using (date, id);