Search code examples
sqlgreatest-n-per-groupgaps-and-islands

SQL: Identifying latest max sequence per row

Current Results:

Current Results

Required Results:

Required Results

I am struggling creating Latest_Funded_Seq flag. The logic behind it is to take the latest max Funded_Seq in case Funded_Seq is NULL

Any help will be much appreciated!

Solution

  • The ANSI standard method uses lag() with the ignore nulls option:

    select lag(funded_seq ignore nulls) over (order by appseq) as latest_funded_seq,
       t.*
from t;

    Even some databases that support window functions do not support ignore nulls, but this is the ANSI standard solution.

    If funded_seq is in order (as in the data in the question), then a cumulative maximum solves the problem:

    select max(funded_seq) over (order by appseq) as latest_funded_seq,
       t.*
from t;

    Cumulative maximums are also ANSI standard. In practice, they are supported by all databases that support window functions.