Search code examples
sqlpivotsnowflake-cloud-data-platformaggregate-functions

Find the latest timestamps for each column where it's not null

I trying to write a single query in an optimal way to get the latest timestamps where each column is not null, grouped by ID.

Let's say we have a table like below:

id timestamp col1 col2
A T1 2 null
B T2 null 4
B T3 5 null
A T4 null 8

Now I want to have something like:

select max(timestamp where column1 is not null), max(timestamp where column2 is not null) from table group by id;

I tried writing separate queries for each column and tried to union them and it worked, but want to know if there's a more optimal way of doing it.

Solution

  • Use case expressions to phrase the conditionals:

    select id,
    max(case when column1 is not null then ts end) as max_column1, 
    max(case when column2 is not null then ts end) as max_column2
from mytable 
group by id

    I renamed the timestamp column to ts so it does not clash with the corresponding SQL keyword.