updated
I have the following table of updates which you will note contains nulls
refer | event_date | col1 | col2 | col3 | cat |
---|---|---|---|---|---|
2 | today | null | null | bbb | a |
2 | yesterday | null | null | null | b |
2 | yesterday | null | xyz | null | b |
2 | last week | abc3 | null | null | z |
2 | two weeks ago | null | null | null | z |
2 | last month | null | def | null | a |
2 | last year | vwy | abc123 | null | z |
so for refer = 2
- bbb is the latest update of col3 based on latest date for cat = a (today).
- xyz is the latest update of col2 based on date for cat = b (yesterday).
- abc3 is the latest update of col1 based on date for cat z (last week).
I had previously asked a similar question here and accepted the following code which was kindly provided by @GMB and works fine if the above table has no NULLS.
select refer,
max(col1) filter(where rn3 = 1 and cat = 'a') detail,
max(col1) filter(where rn2 = 1 and cat = 'b') detail2,
max(col1) filter(where rn1 = 1 and cat = 'z') detail3
from (
select t.*,
row_number() over(partition by refer,col3 order by event_date desc) rn1,
row_number() over(partition by refer,col2 order by event_date desc) rn2,
row_number() over(partition by refer,col1 order by event_date desc) rn3
from mytable t
) t
group by refer
expected output:
refer | col1 lastest | col2 latest | col3 latest |
---|---|---|---|
2 | bbb | xyz | abc3 |
The thing is it doesn’t work if there are nulls in the columns and I cant figure out how to eliminate nulls from the window partition groups. If I could add is not null in the partition as shown below it would fix my problem eg:
select
row_number() over(partition by refer, col3 **(where col3 is not null)** order by event_date desc) rn1
,row_number() over(partition by refer, col2, ***(where col2 is not null)*** order by event_date desc) rn2
,row_number() over(partition by refer, col1, ***(where col1 is not null)*** order by event_date desc) rn3
2
Answers
To eliminate
null
values from being captured from all partitions, use the conditional order insiderow_number()
, Also partitions must be based on refer and cat:Result :
Demo here
No need to check for null values: they’re removed by the max. You can move all your conditions inside the
ORDER BY
clause of theROW_NUMBER
window functions, then you can apply conditional aggregation in an easier way.Output:
Check the demo here.