Skip to content
Advertisement

Left join only on first row

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);
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement