How to get the first non-empty value from a column of values ​​in Big Query?

Question

I am trying to extract the first non-empty value from a column of values ​​based on a timestamp. Can someone share their thoughts on this. Thanks.

What have i tried so far?

FIRST_VALUE( column ) OVER ( PARTITION BY id ORDER BY timestamp) 

Input :-

id,column,timestamp
1,NULL,10:30 am
1,NULL,10:31 am
1,'xyz',10:32 am
1,'def',10:33 am
2,NULL,11:30 am
2,'abc',11:31 am

Output(expected) :-
1,'xyz',10:30 am
1,'xyz',10:31 am
1,'xyz',10:32 am
1,'xyz',10:33 am
2,'abc',11:30 am
2,'abc',11:31 am

Answer 1

Try this old string manipulation trick:

Select 
ID,
  Column,
  ttimestamp,
  LTRIM(Right(CColumn,20)) as CColumn,
  FROM
(SELECT
  ID,
  Column,
  ttimestamp,
  MIN(Concat(RPAD(IF(Column is null, '9999999999999999',STRING(ttimestamp)),20,'0'),LPAD(Column,20,' '))) OVER (Partition by ID) CColumn
FROM (

  SELECT
    *
  FROM (Select 1 as ID, STRING(NULL) as Column, 0.4375 as ttimestamp),
        (Select 1 as ID, STRING(NULL) as Column, 0.438194444444444 as ttimestamp),
        (Select 1 as ID, 'xyz' as Column, 0.438888888888889 as ttimestamp),
        (Select 1 as ID, 'def' as Column, 0.439583333333333 as ttimestamp),
        (Select 2 as ID, STRING(NULL) as Column, 0.479166666666667 as ttimestamp),
        (Select 2 as ID, 'abc' as Column, 0.479861111111111 as ttimestamp)
))

Answer 2

As far as I know, Big Query does not have parameters such as "IGNORE NULLS" or "NULLS LAST". Given this, this is the easiest solution I could come up with. I would like to see even simpler solutions. Assuming the input is in the table "original_data",

select w2.id, w1.column, w2.timestamp
from
(select id,column,timestamp
   from
     (select id,column,timestamp, row_number() 
                   over (partition BY id ORDER BY timestamp) position
       FROM original_data
       where column is not null
    )
   where position=1 
) w1
right outer join
 original_data as w2
on w1.id = w2.id 

Answer 3

You can change your sql like this to get the data you need.

FIRST_VALUE( column )
  OVER ( 
    PARTITION BY id
    ORDER BY
      CASE WHEN column IS NULL then 0 ELSE 1 END DESC,
      timestamp
  )

Answer 4

SELECT id,
  (SELECT top (1) column FROM test1, where id = 1, and the column is not null order using autoID desc) as the name, FROM timestamp is yourTable

Exit: - 1, 'xyz', 10:30 1, 'xyz', 10: 31 1, 'xyz', 10: 32 am 1, 'xyz', 10: 33 2, 'abc', 11:30 2 , 'abc', 11:31 a.m.