1

I have a table that has something like this:

dt              col1 col2
2000-01-01      null null
2000-01-02      null null
2000-01-05      null null
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

Note it has an unpredictable number of records and in some cases I want to query the table and trim off the records that are earliest in the time series that are all null that occur prior to the first record that has at least one value. In this example I want to remove records 2000-01-01 through 2000-01-05. Note that I want to leave all records whether all values are null or not after the first one is found that has a non-null value starting from oldest date forward.

So my desired result would be:

dt              col1 col2
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

This doesn't work because it removes all records that have all null values:

select 
    * 
from 
    tbl
where
    col1 is not null 
    or col2 is not null;

Is there a way to solve this without changing the table at all (doing it all in a query)?

My alternative is to iterate through the records in a program that this query is needed. I'm thinking SQL might be better.

I'm using PostgreSQL 9.6.

1 Answer 1

2
select * from tbl
where dt>= (select min(dt) from tbl where col1 is not null or col2 is not null)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.