CODEWITHSUNDEEP
sqlgoogle-bigquerypartitionrow-number
Kamila Nurlanova
Kamila Nurlanova

Reputation: 35

How to select very first non-null date BigQuery

I want to select very first non-null datetime but it doesn't work for me with using

"row_number() over partition()"

In my code now I just excluded null rows and now I need to select only the lowest date. Pls.help how to fix it:

left join (
          select deal, type, assigned_to_user, marked_as_done_time,
          first_value(marked_as_done_time ignore nulls) over (partition by deal
          order by marked_as_done_time asc ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as first_value,
          
          from data_marts.pipedrive_activity
          where done = 'Done' 
          group by 1,2,3,4
          ) act on act.deal = d.id

Upvotes: 0

Views: 205

Answers (1)

Fahmi
Fahmi

Reputation: 37483

You can try the below -

left join (
          select deal, type, assigned_to_user, marked_as_done_time,
          row_number() over (partition by deal
          order by marked_as_done_time) as first_value,
          
          from data_marts.pipedrive_activity
          where done = 'Done' and marked_as_done_time is not null
          group by 1,2,3,4
          ) act on act.deal = d.id where first_value=1

Upvotes: 0

Related Questions