1

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 aab null null z
2 last month null def AAA a
2 last year vwy aa123 null b

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
earbasher
  • 39
  • 6
  • Can you add the corresponding expected output? – lemon May 23 '23 at 15:11
  • Thanks for assistance, I've not got my question correct I will edit and repost – earbasher May 23 '23 at 15:53
  • @lemon not yet but good try from SelVazi – earbasher May 24 '23 at 08:45
  • Probably your sample data does not match the nature of your actual data. Consider checking where the solutions fail, and understand what makes them fail. Then update your data so that it would cause problems with these solutions. – lemon May 24 '23 at 09:00
  • My solution [works with this data](https://www.db-fiddle.com/f/tVrkqWS51RVHCbtoAm7cnb/2) tho, unless the expected output is not yet updated. – lemon May 24 '23 at 09:12
  • I agree your solution is working, I need to further investigate why it doesn't with 'real' data ...... – earbasher May 24 '23 at 09:17

1 Answers1

2

To eliminate null values from being captured from all partitions, use the conditional order inside row_number(), Also partitions must be based on refer and cat:

select refer,
    MAX(col3) filter(where rn3 = 1 and cat = 'a') col1_lastest,
    MAX(col2) filter(where rn2 = 1 and cat = 'b') col2_lastest,
    MAX(col1) filter(where rn1 = 1 and cat = 'z') col3_lastest
from (
    select t.*,
      row_number() over(partition by refer,cat order by case when col3 is not null then 1 else 2 end, event_date desc) rn3,
      row_number() over(partition by refer,cat order by case when col2 is not null then 1 else 2 end, event_date desc) rn2,
      row_number() over(partition by refer,cat order by case when col1 is not null then 1 else 2 end, event_date desc) rn1
    from mytable t
) t
group by refer;

Result :

refer col1_lastest col2_lastest col3_lastest
2 bbb xyz abc3

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29