Search code examples
sqllag

SQL Lag function and where clause

I'm new in SQL and I want to write a query where we keep the rows where, for the same item:

  • column1_value is NULL AND
  • the previous not null column1_value is 'end' OR
  • the previous column1_value is NULL

Everything else is discarted.

I know I have to use LAG function i've not been able to reach to a final query. Can you help?

Thanks in advance

Solution

  • If I understand correctly:

    select t.*
from (select t.*,
             lag(column1) over (order by ordercol) as prev_column1
      from t
     ) t
where column1 is null and 
      (prev_column1 = 'end' or prev_column1 is null)