I have the following sample query:
x
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);